As you know developers life is not so easy. 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:
ALTER Procedure ParamToDynamicSQL(@bActive bit) AS BEGIN DECLARE @Article TABLE ( ID bigint, CategoryID bigint, Title varchar(500), Published datetime, ModifedDate datetime, Active bit, TotalView bigint ) DECLARE @sSQL NVARCHAR(MAX) DECLARE @ParameterList NVARCHAR(1000) SET @ParameterList = '@bActive bit' SET @sSQL='SELECT * FROM Article WHERE Active=@bActive' INSERT @Article EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive SELECT * FROM @Article END
Now you can invoke the above SP like below:
EXEC ParamToDynamicSQL 1
This is all about Dynamic SQL. Hope now you can write runtime Dynamic SQL in SQL Server Stored Procedure (SP) to meet the client requirements.