How one can pass OUTPUT Parameter to Dynamic SQL

Some times we need to pass Output parameter to a Stored procedure. But using this Output parameter into the Stored procedure Dynamic SQL is not so easy. In this article I will try to show you how we can use OUTPUT parameter in Dynamic SQL. Lets now our requirement is to show number of active articles. So we need to write a SP like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit,@TotalCount int OUTPUT)

DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit,@TotalCount int OUTPUT'

SET @sSQL='SELECT @TotalCount=COUNT(*) FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive,@TotalCount=@TotalCount OUTPUT


And you can invoke the Stored procedure in the following way:

Declare @TotalCount int
EXEC ParamToDynamicSQL 1,@TotalCount OUTPUT
print @TotalCount
Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *