CODEWITHSUNDEEP
sqlsql-serverif-statementcursor
cdub
cdub

Reputation: 25731

IF EXISTS in SQL Server Cursor Not Working

I have a cursor which works fine but when it gets to this part of the script, it seems to still run the update even though the table doesn't exists:

 SET @sql = 'IF (EXISTS (SELECT * FROM ps_vars_' + @datasetid + '))
             BEGIN
     UPDATE ps_vars_' + @datasetid + '
     SET    programming_notes = replace(programming_notes, ''Some of the variables listed are source variables.'')
         END';

  EXEC SP_EXECUTESQL @sql

What am I missing? The #datasetid variable gets passed in correctly too.

Upvotes: 0

Views: 2194

Answers (2)

Martin Smith
Martin Smith

Reputation: 453727

DECLARE @tablename sysname 

SET @tablename = 'ps_vars' + @datasetid

IF (OBJECT_ID(@tablename, 'U') IS NOT NULL)
BEGIN
 SET @sql = ' UPDATE ' + QUOTENAME(@tablename) + '
     SET    programming_notes = replace(programming_notes, ''Some of the variables listed are source variables.'') ';
     EXEC sp_executesql @sql  
END

Upvotes: 2

Tom H
Tom H

Reputation: 47392

When you use the EXISTS with the table name to see if the table exists you're actually trying to access the table - which doesn't exist. That's why you're getting an error, not because of your UPDATE statement.

Try this instead:

SET @sql = 'IF (OBJECT_ID(''ps_vars_' + @datasetid + ''') IS NOT NULL)
    BEGIN
        UPDATE ...
    END'

Then think about what might be wrong with your database design that requires you to use dynamic SQL like this. Maybe your design is exactly how it needs to be, but in my experience 9 out of 10 times (probably much more) this kind of code is a symptom of a poor design.

Upvotes: 1

Related Questions