Case with Update statement Or Conditional Update

There are certain situation where we need some updation  on the basis of condition. For example  if name contain ‘Kruthika’ update it to ‘Kruthik’.

Our table is containing the following records

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

You might be thinking this update can be accomplished with the help of were condition in update statement. Correct it goes like this

update user_tab

set user_name = 'Kruthik'

where user_name = 'Kruthika'

(1 row(s) affected)

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthik                                            1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

But consider a situation where you need to update ‘Trishona’ with ‘Trishon’ and ‘suranjan’ with ‘suranjana’.

Is it possible with WHERE clause in Update statement. ANSWER IS NO.

Then what is option left for such update, Here comes CASE, let see how this update can be done with CASE

update user_tab

set user_name = (case user_name

                              when 'Trishona' then 'Trishon'

                              when 'suranjan' then 'suranjana'

                              else user_name end)

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishon                                            1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjana                                          1

(11 row(s) affected)

I really like this keyword a lot of thing can be accomplished with CASE.

Post Reference: Vikram Aristocratic Elfin Share

How to call a user defined function with a default parameter

Today it happened one of my team member called up me and ask me how to call his function where he has used default parameter. The snapshot of his function is like this only..

create function fnTest(@param1 int, @param2 int = 1 )

returns int

as

begin      

      return @param2      

end

His question was how to call this function in select statement, I simple said, u can either pass two parameter while calling this function, if you are doing so then the second default parameter @param2 value  will get replaced by the new one you pass while calling this function.

select dbo.fnTest(2,5)

-----------

5

(1 row(s) affected)

Where as  if you pass only one parameter to the calling funtion then the @param2 will take the dufault value as 1 in this case.

I give him the below mention line for calling the funtion with one parameter as soon as we excute this line of code we got  error message

select dbo.fnTest(7)

Msg 313, Level 16, State 2, Line 1

An insufficient number of arguments were supplied for the procedure or function dbo.fnTest.

Belive me I was not aware of this then somebody from other team sitting behind us told to use default keyword while calling funtion with default parameter.

select dbo.fnTest(1,default)

-----------

1

(1 row(s) affected)

Thanks Shekhar for the solution.

Post Reference: Vikram Aristocratic Elfin Share

Make use of Magic tables with output clause while inserting/updating or Deleting record in your database table objects

There are two magic table provided by SQL server- Inserted and Deleted.

Inserted and Deleted: The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert transaction, new rows are added to the inserted table.  In case of update transaction which is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table.

OUTPUT Clause: The clause returns a copy of the data that you’ve inserted into or deleted from your tables.  OUTPUT clause has accesses to inserted and deleted tables, OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

Let’s explore this with the help of an example:

For our explanation we have taken a table ‘User_Tab’ having the following records in it.

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Fani                                               1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

(11 row(s) affected)

INSERT : Now we are trying to insert a new record in it. See the below mentioned code, here we are using OUTPUT clause to show the value which are going to inserted into the table

insert into user_tab(user_name,active)

output INSERTED.user_id,INSERTED.user_name

values ('suranjan','True')

user_id     user_name

----------- --------------------------------------------------

16          suranjan

(1 row(s) affected)

DELETE: Here we are using OUTPUT clause with Deleted magic table of Sql Server to fetch the deleted value.

DELETE user_tab

OUTPUT deleted.user_id,deleted.user_name

WHERE user_name='suranjan'

user_id     user_name

----------- --------------------------------------------------

16          suranjan

(1 row(s) affected)

UPDATE:  In case of update, the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table. See the below code

update user_tab

set user_name = 'Phaneesh'

output deleted.user_name as 'PreviousName' ,inserted.user_name as 'NewName'

where user_name = 'Fani'

PreviousName                                       NewName

-------------------------------------------------- --------------------------------------------------

Fani                                               Phaneesh

(1 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share