Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-23
9
Medium Priority
?
640 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
  • 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 36

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 36

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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