Sunday, September 14, 2014

A Interview question How to determine what value you have updated of a column ? A output clause example #TIP 48

 

Problem:-

Sometimes it may require that whatever you have updated you want to know what was the previous value of that column?

It was asked by many interviewer in the interview how to avoid trigger or how to know what was previous value before update ?

Solution:-

Now to achieve this the basic step is before updating the record select those value and have it in a temporary variable.

But We are doing here by a new feature (not exactly new introduced in SQL Server 2005) which is OUTPUT CLAUSE

Suppose I have tbluser table in which a user record with id 1 having userame “staff”

Now I have to update this in capital letter. To achieve this we have to write following query

UPDATE tblUser
SET UserName = 'STAFF'
OUTPUT INSERTED.UserName,
   deleted.UserName
WHERE userId= 1
   

When you run it you will get output as shown below

Output_Clause

 

I hope it may helpful you somewhere enjoy !!!

RJ!!!

CONCAT a valuable function TIP #47

 

This is one  the new feature in SQL Server 2012 by which you can concatenate values.

The USP of the function is that it handle the NULL values also.

means if you are concatenating values in which null also exists then it handle those null value also means it concatenates only those values which does not have null.

For example

Suppose I want to concatenate the column like product name, product number, color and class of Product table of adventureWorks database and it might be possible that class & color may have null value. So we don’t need to do extra care of  those nullable column. We can write simple query as shown below

Concate

I hope this feature may help you somewhere in your technical life.

Thanks & Enjoy Smile

Rj !!