Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to use case statement or conditional if in dynamic in SQL

Posted on 2014-04-23
9
Medium Priority
?
612 Views
Last Modified: 2014-05-16
I have defined the following cursor below.  The select portion is complete, no problems there... what I want to know is how to define a case statement or conditional sql that will allow the cursor to look at to different values for VEHICLE_ID2_FW  and update with the respective value.   So you see the first SET statement for @sql value which will hold concatenated update statement for each table that I am reading in sys.objects along with the set statement for to update the actual value of VEHICLE_ID2_FW.    Will I have to duplicate the SET block in bold below or can I incorporate a case statement?

Just to be clear.  I am updating all tables with the column VEHICLE_ID2_FW.  I have two vehicle id's (tags) that I need to updated.  So loop through the tables, and set the VEHICLE_ID2_FW based on the values below.

                               OLD                      NEW
                               AA12345               12345AA
                               BB23456               23456BB


open C
fetch next from C into @tablename

while @@fetch_status = 0
begin
      SET @sql = 'update ' + @tablename +
                ' set VEHICLE_ID2_FW= CASE''12345AA'' where VEHICLE_ID2_FW=''AA12345'''
      EXEC (@sql)

    fetch next from C into @tablename
end

close C
deallocate C
go
0
Comment
Question by:66chawger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40017782
I would stay from Cursors, they're evil! : )
The following is far more efficient:

Update <my-table>
Set VEHICLE_ID2_FW = Case OLD when 'AA12345' then '12345AA'
                              when 'BB23456' then '23456BB
                     end
where VEHICLE_ID2_FW in ('AA12345', 'BB23456')
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40017785
No CASE needed here. It's a simple WHERE:

SET @sql = 'UPDATE ' + @tablename + ' SET VEHICLE_ID2_FW = ''12345AA'' WHERE VEHICLE_ID2_FW =''AA12345'';'

Open in new window


As normal SQL:

UPDATE  tablename
SET     VEHICLE_ID2_FW = '12345AA'
WHERE   VEHICLE_ID2_FW = 'AA12345';

Open in new window


btw, when this value is really used in more tables and it really is an identifier, then this kind of update should not work due to foreign key constraints. When there are none, then your model is flawed.
0
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40017799
Correction:
Update <my-table>
Set VEHICLE_ID2_FW = Case VEHICLE_ID2_FW  when 'AA12345' then '12345AA'
                              when 'BB23456' then '23456BB
                     end
where VEHICLE_ID2_FW in ('AA12345', 'BB23456')

It's important to keep the WHERE clause in sync with the CASE to ensure you won't overwrite the rest of the data with NULL.

Alternatively if the Old and New values are already stored in another table then you can use a simple join to update your table:

Update <My-table>
Set VEHICLE_ID2_FW = New
From  <My-table> T1 inner join <The-Other-Table> T2
On T1.VEHICLE_ID2_FW = T2.Old
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:66chawger
ID: 40018066
ste5an, your solution is simple but only for one value.  I am updating all tables with a specific column name "VEHICLE_ID2_FW".   A case statement is needed because I am updating two different values as indicated in my original description

                               OLD                      NEW
                               AA12345               12345AA
                               BB23456               23456BB

So as I select a table from the cursor, I have to update all the OLD values with the NEW values.
0
 

Author Comment

by:66chawger
ID: 40018102
ProjectChampion,

I don't like cursors either,  but how to resolve because of having to update all tables with the specific column with the new values.   Here is the entire SQL script, and I am not particularly fond of it.  Also need a rollback added.

declare @tablename sysname
declare @sql varchar(500)

declare C cursor fast_forward for
SELECT name FROM sys.objects WHERE object_id IN (
    SELECT object_id FROM sys.columns WHERE Name = N'VEHICLE_ID2_FW'
) AND name <> N'sysdercv'

open C
fetch next from C into @tablename

while @@fetch_status = 0
begin
      SET @sql = 'update ' + @tablename +
               ' set VEHICLE_ID2_FW = Case VEHICLE_ID2_FW when ''AA12345'' then ''12345AA''
                  when ''BB23456'' then ''23456BB''' +
               ' end' +
               ' where  VEHICLE_ID_FW in (''AA12345'', ''BB23456'')'
        EXEC (@sql)
    fetch next from C into @tablename
end

close C
deallocate C
go
0
 
LVL 41

Expert Comment

by:Sharath
ID: 40018718
Do you have more such values or only these two?
How many tables are you updating?
This can be done without cursors.
0
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 1000 total points
ID: 40019404
Hi 66chawger,
I see your point for using a cursor in this case... and I agree it's not ideal. Anyhow as far as the update is concerned the latest edition of your script looks fine.

I also agree with you about carrying out the updates in the context of a transaction; that's very sensible as it'll prevent premature updates. In that case I'd suggest you use your script only for generating the update queries and not for running them. When you get all the necessary update scripts generated then just put it in try catch block like the following:
begin try
begin tran
   Update ...
   ...
   Update ...
commit tran
end try
begin catch
  rollback tran
end catch;

Then you'll have a chance to see and check that the scripts make sense before it's run.

If this is not a one-off practice and you need to automate the whole process, then I'd suggest you generate the whole script (including all updates in a transaction block) first and then execute it in a second step.
0
 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 1000 total points
ID: 40019410
As this should a one-time action I would do it like this:

DECLARE @Map TABLE
    (
      OldValue NVARCHAR(255) ,
      NewValue NVARCHAR(255)
    );

INSERT  INTO @Map
VALUES  ( N'AA12345', N'12345AA' ),
        ( N'BB23456', N'23456BB' );

DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'
UPDATE  T
SET     VEHICLE_ID2_FW = M.NewValue
FROM    tablename T
        INNER JOIN @Map M ON T.VEHICLE_ID2_FW = M.OldValue;
';

SELECT  REPLACE(@Sql, 'tablename', QUOTENAME(S.name) + '.' + QUOTENAME(T.name))
FROM    sys.tables T
        INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
        INNER JOIN sys.columns C ON C.object_id = T.object_id
WHERE   C.name = N'VEHICLE_ID2_FW'
        AND T.name <> N'sysdercv';

Open in new window


And execute the result set via copy'n'paste in the same SSMS query window.
0
 

Author Closing Comment

by:66chawger
ID: 40070573
Project Champion and ste5an, thanks very much for your input... very sorry for the lengthy delay in closing out this topic... health issues.
Anyway, yes cursors are not my choice of venue...so to speak...  however, both your solutions offer a choice.   This gives me leverage going forward.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question