Using Parameters With Dynamic T-Sql

It’s a simple technique but I haven’t often used it. The documentation was a bit disappointing, so this is an aide-memoire which (I hope) makes things simpler. The stored procedure sp_executesql does the job.

Its first parameter is the sql, the second defines the parameters to be passed in, and after that come the parameters themselves (in the same order as the definition of course). The following query is an example.

SET NOCOUNT ON

DECLARE @crlf nchar(2) = CHAR(13) + CHAR(10)

--sql statement--
DECLARE @Sql nvarchar(1000) = 'CREATE TABLE T1(C1 int NOT NULL)' + @crlf + 'INSERT INTO T1(C1) VALUES(@Data);' + @crlf + 'SET @RowCount = @@ROWCOUNT' + @crlf + 'DROP TABLE T1'

--input parameter--
DECLARE @Data int = 1

--output parameter--
DECLARE @RowCount bigint

--parameter definition for sp_executesql--
DECLARE @Params nvarchar(1000) = N'@Data int, @RowCount bigint OUTPUT'

/*stored procedure call*/
EXEC sp_executesql
--sql statement comes
@Sql
--parameter definitions--
,@Params
--parameters--
,@Data, @RowCount OUTPUT

/*results*/
PRINT '***@Sql***' + @crlf + @Sql + @crlf + @crlf
PRINT '***@Params***' + @crlf + @Params + @crlf + @crlf
PRINT '***@RowCount***' + @crlf + CAST(@RowCount as nvarchar(1))

As you can see above, for any output parameters you need to include the “OUTPUT” keyword in the definition that you pass in to the procedure, and also in the procedure call itself. Hope this makes everything simple.

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 )

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 )

w

Connecting to %s