How to Set SQL Variables in Dynamic SQL Text


The following example shows how to use variables for input and output for execution of sql text.  This is handy for stored procedures that use sp_executesql for executing query text against a database.

-- Declare Input/Output Variables
Declare @vc_sql 		nvarchar(4000)	-- MUST BE NVARCHAR
	,@vc_input_Name  	varchar(200)
	,@sdt_output_BirthDay 	Smalldatetime

select @vc_sql = '
Select @sdt_output_BirthDay  = Employee.Birthday
From  dbo.Employee (NoLock)
where  Employee.Name >= ''@vc_input_Name'''

exec sp_executesql 	@vc_sql
			,N'@vc_input_Name varchar(200), @sdt_output_BirthDay Smalldatetime output'
			,@vc_input_Name
			,@sdt_output_BirthDay out
Advertisements

About Brian McKay
Software Architect

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: