Solved

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

Posted on 2014-04-23
9
598 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 34

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 250 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 34

Assisted Solution

by:ste5an
ste5an earned 250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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