Setting parameters

Question

Setting parameters

KaitlynTran

SSC Veteran

Points: 244
More actions
September 6, 2002 at 5:53 pm

#79117

I can't seem to figure this out. This is basically what I want to do:
Set @sqlqry = EXEC (@SQLCMD)
Is it possible to do something like that? I want to set my variable @SqlQry to equal the value that is returned by the EXEC (@SQLCMD) statement. The @SQLCMD is a query that is built dynamically. Can this be done?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

You cannot do like this with EXEC. You can however use sp_executesql with output variable. There are several threads on this but I cannot seem to find one right now.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

This is it I think:

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=1664&FORUM_ID=8&CAT_ID=1&Topic_Title=Detecting%20changes%20during%20insert/update&Forum_Title=T-SQL

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn

KaitlynTran SSC Veteran Points: 244 More actions · Answer 3

Thanks for the link. I tried it out but it's still not returning the value back into my variable.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Here is another example I have put together for my example website. Maybe someday I will put this on my site.

This example shows how to use sp_executesql to set a variable in the calling routine.

/*

Occassionally you need to build dynamic T-SQL that not only requires T-SQL code to be dynamic,

but requires this dynamic T-SQL to return variables to the calling procedure. This code

shows a method of setting and returning a variable using the sp_executesql command.

This objective of this code is to return the numbers of records for a given table.

The table is defined by setting the @TABLE variable. The number records is

returned from the sp_executesql store procedure in variable @RECORDCNT.

In order to make this code work you need to pass not only the command you need to execute

to the sp_executesql store procedure, but you also need to send parameters that will define

and initialize the variables you pass in the command.

Look at the call to sP_execute sql below.

The calls first parameter is the dynamic sql command being executed (@cmd). The second

parameter contains the definitions for the variables (@RECORDCNT, @TABLE) used in the dynamic sql.

The third and forth parameters specifies the values of the parameters defined in the second

parameter.

Note: dynamic SQL is not always the most efficient code, and has inherent security risks.

Please review the following article to understand issues related to dynamic SQL code.

http://www.sqlservercentral.com/columnists/rmarda/dynamicsqlversusstaticsqlp1.asp

http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful.asp

*/

-- Returning variables from sp_executesql

-- Written by: Greg Larsen Date: 08/21/2002

-- use the Northwind database

use northwind

go

-- Declare variables

declare @RECORDCNT int

declare @TABLE char(100)

declare @cmd Nvarchar(100)

-- Set the table to be used

set @TABLE = '[ORDERS]'

-- Build the dynamic T-SQL command

SET @CMD = 'select @RECORDCNT=count(*) from ' + @TABLE

-- Call the sp_executesql SP to return the record count for @TABLE variable

exec sp_executesql @CMD,N'@RECORDCNT int out, @TABLE char(100)',@RECORDCNT out , @TABLE

-- Display the number of records

print @RECORDCNT

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

KaitlynTran SSC Veteran Points: 244 More actions · Answer 5

Thanks everyone for your help! This helped me a great deal, i was able to set my parameter to the result value. Thanks again!