Solved

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

Posted on 2014-04-23
9
572 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 33

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 40

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 33

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
Managing Columnstore Indexes 2 29
convert null in sql server 12 34
Find results from sql within a time span 11 31
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

772 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