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.
blog.philipsymons.com 