Passing variable from sp_executesql

Question

Post reply

Passing variable from sp_executesql

Vic-250470

SSC Rookie

Points: 42
More actions
August 4, 2005 at 6:45 am

#66479

I am trying to execute a certain batch of commands using sp_executesql function available in MSSQL, but I somehow need to return a 'result' code back to the calling procedure. I understand from MSSQL's help that the commands in sp_executesql are sent to the server as a separate batch, and as such cannot directly reference calling procedure's variables.
I was wondering if there is a way of returning such 'result' code without having to physically write it to some location / table in the DB which the calling procedure would then read (I am trying to avoid this on account of processing speed as well as because the calling procedure may be executed simultaneously by a number of users, so I would need to somehow separate each instance when returning the code)
Any help on this would be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

This is the sort of syntax I usually use:

declare @rows1 int

exec sp_executesql N'select @rows2 = count(*) from master..sysobjects', N'@rows2 int output', @rows2 = @rows1 output

select @rows1

I've used variable names @rows1 and @rows2 here just for illustrative purposes. Usually I'd use just one variable name - eg. @rows - throughout. eg.

declare @rows int

exec sp_executesql N'select @rows = count(*) from master..sysobjects', N'@rows int output', @rows output

select @rows

Cheers,
- Mark

Vic-250470 SSC Rookie Points: 42 More actions · Answer 2

Thanks Mark! This is excellent and works very well!

Would you mind clarifying for me how the command & parameters are constructed? I assume the use of "output" command is what does the trick, but I would appreciate understanding how the interaction between the two command batches (the calling batch and the sp_executesql batch) works.

Thank you.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

sp_executesql works much the same way that a stored procedure would, the declaration of in, and out parameters is a little different, but concept is the same, except that the code to execute is prepared at execute time.

From books online

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp

Breakdown

EXEC sp_executesql @InsertString,

N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,

@InsOrdMonth INT, @InsDelDate DATETIME',

@PrmOrderID, @PrmCustomerID, @PrmOrderDate,

@OrderMonth, @PrmDeliveryDate

@InsertString is the tsql statement to execute

these are the parameter declarations, the parameters that are used in the Tsql statement

'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,

@InsOrdMonth INT, @InsDelDate DATETIME'

-- In Parameters

@PrmOrderID, @PrmCustomerID, @PrmOrderDate,

@OrderMonth, @PrmDeliveryDate

These are the values to use inside the Embedded Parameters. You don't have to pass parameters in, you could also just provide explicit values.

Like so

@PrmOrderID, 18, @PrmOrderDate,

11, '10/15/2005'

More than you ever wanted to know about dynamic sql

http://www.sommarskog.se/dynamic_sql.html

Vic-250470 SSC Rookie Points: 42 More actions · Answer 4

Thanks to all for help on this one. I also found the following reference, which is useful if anyone's interested in this issue:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_3q7n.asp