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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s


%d bloggers like this: