Solved

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

Posted on 2014-04-23
9
587 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I get the entire database script? 7 34
Using this function 4 54
Database Owner 3 47
Database Mail Profiles 1 52
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 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