How one can pass an Input Parameter to Dynamic SQL

In my Dynamic SQL introductory article i have described how to write Dynamic SQL. Here in this article i will show you how you can use input parameter in a Dynamic SQL.

To do that first declare a NVARCHAR type variable to store all parameters & then pass it through SP_EXECUTESQL method like below:

CREATE Procedure ParamToDynamicSQL(@bActive bit)

DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'

SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive


Now run the above SP by invoking the below command:

EXEC ParamToDynamicSQL 1

An Output:
Input Parameter

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code