How easy or difficult to write dynamic SQL ? TIP#110

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 !!!

An interesting setting for NULL but don’t use it for future TIP #109

As we discussed earlier in TIP#103 for NULL in which I shared that we have to take extra care for NULL.

Now in this tip I would like to share one of the interesting setting for NULL. Although it is just for knowledge but don’t use it because it is deprecated in future version and by default you this setting is always on.

Still you are thinking for which setting I am talking about.

So , I am talking about “CONCAT_NULL_YIELDS_NULL”  . Let’s understand it by following example . By Default CONCAT_NULL_YIELDS_NULL is ON which means if anything added to NULL will be null as discussed earlier in our tip #103

Now, see what happens when we do it OFF.

So, We clearly saw in above image  when we set the CONCATE_NULL_YIELDS_NULL property to OFF it dissolved the NULL property.

My take on this property is that we should avoid it don’t try to make it OFF explicitly because if we do this then we explicitly breaking some hidden business rules.

I hope you will like this post.

Enjoy !!!

RJ !!