This is one of the challenge for most of the developer to write dynamic SQL. Generally we follow the approach of string concatenation.
This seems very easy but we need to cast the parameters in VARCHAR and sometimes we stuck in single code.
I am sure this happened with all of us.
Let’s understand first a straight forward way which we (most of us) are using.
In example I am using person table of Adventureworks database and it is just a simple query which provide person row according to primary key.
Now the above query is OK but it can be write in much better way with one of the SQL server in build stored procedure which is sp_executeSQL. This is one of the best way which have certain advantage which will discuss in next tip. Now see how we can write above query in much better way
DECLARE @BusinessEntityID INT
DECLARE @DynamicSQL NVARCHAR(200)
DECLARE @Parameters NVARCHAR(100)
SET @BusinessEntityID = 1
SET @Parameters =N'@ParameterBusinessEntityID INT'
SET @DynamicSQL = N'SELECT * FROM [Person].[Person] WHERE BusinessEntityId = @ParameterBusinessEntityID '
EXECUTE sp_executesql @DynamicSQL, @Parameters, @ParameterBusinessEntityID =@BusinessEntityID
If you see above query, you will find we are not using any type conversion the statement is clear.
This is one of the best way to write dynamic query which is not only increase your statement’s readability but also increase performance of your query with certain amount.
Which we will discuss in next tip.
I hope now you are eager to use this and replace all your old fashion dynamic queries.
Please do post your feedback.
Enjoy !!!
RJ !!!
