Now our environment is ready for testing. Let our requirement is list all articles based on Active or not. So need to write a stored procedure which will take one parameter for Active or inactive & then we need to pass dynamic sql result into a temprary table.

How one can pass an Input Parameter to Dynamic SQL:To do that first declare a NVARCHAR type variable to store all parameters & then pass it through SP_EXECUTESQL method like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit)ASBEGIN

DECLARE @sSQL NVARCHAR(MAX)DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'

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

END

Now run the above SP by invoking the below command:

EXEC ParamToDynamicSQL 1

Fig: OUTPUT

How one can pass OUTPUT Parameter to Dynamic SQL:So I think initial work around is done. Now 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 modify our previous SP like below:

Ok now I hope you can pass parameter value into Dynamic SQL as well as can retrieve OUTPUT parameter value from Dynamic SQL.

How one can store Dynamic SQL data into a Temporary table:As you know developers life is not so easy. The above techniques may not ease your life. We know that if we want to write a complex SQL then we like to break this SQL in different parts. To do that we use either view or temporary table to break down the complex SQL which will more readable & easy to modify. Here I will show you how we can store Dynamic SQL OUTPUT into temporary table. So that you can use this temporary table with another table to make SQL JOINS like Inner Join, Left Join & Right Join also you can then apply SET operation. One of the examples is given below: