Pass a value from dynamic sql to the calling scope

–declare a variable which will hold your sql and populate it
–note that because we want to pass @time_inner to scope of the call
we do not need to declare it as a variable
–instead we use it as an output parameter which define later

DECLARE @sql NVARCHAR(MAX) = ‘set @time_inner = getdate()’ –sql must
be unicode hence the N

DECLARE @time_outer DATETIME –if are going to pass the output to the
calling scope we need somewhere to store it – @time_outer

EXEC sp_executesql @sql, N’@time_inner datetime OUTPUT’,@time_inner =
@time_outer OUTPUT
–the second parameter is a list of parameters used by the code you
are executing
–in this case only one which we mark as OUTPUT
–the third parameter is where we assign the output to a variable in
the calling scope, mark it output
–if there are any further outputs they can be assigned in additional parameters

PRINT @time_outer
–proove it has worked by printing the calling scope variable


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: