Avatar of Amour22015
Amour22015
 asked on

SQL Server - Stored Procedure, If Exits - Delete Constaint & Tablel

Hi Experts,

I am very new to all this.

I need help this is giving me errors.

I am passing parameters from SSIS to the SP in SQL Server 2008 R2
      @fileId int,
      @TBLNM varchar(40),

That is working fine I can tell by the test I do:
      PRINT 'STARTING DELETION OF: ' + @TBLNM
      PRINT 'THIS IS THE VALUE OF FILE_ID: ' + cast(@fileId as varchar(20))


I would like to first check if a Constaint exist, if it does delete and the table.

I have this code:
      --DELETE CLAIM TABLE(S)
      PRINT 'STARTING DELETION OF: ' + @TBLNM
      PRINT 'THIS IS THE VALUE OF FILE_ID: ' + cast(@fileId as varchar(20))
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
            SELECT @sql = 'IF OBJECT_ID  (''dbo.' + @TBLNM + ''') IS NOT NULL  ' +
           'ALTER TABLE dbo.' + @TBLNM + ' DROP CONSTRAINT CK_' + @TBLNM + '_Max_File_ID ;' +
           ' DELETE FROM ' + @TBLNM + ' AS T' +
           ' WHERE T.[FILE_ID] = ' + cast(@fileId as varchar(20))+ ' AND
                        EXISTS(SELECT #CLAIM_TEMP.CLM_KY
                                 FROM #CLAIM_TEMP
                                 WHERE #CLAIM_TEMP.CLM_KY = T.CLM_KY AND
                                       #CLAIM_TEMP.FILE_ID = ' + cast(@fileId as varchar(20)) + ')'
                  exec sp_executesql @sql;
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      PRINT 'ENDING DELETION OF: ' + @TBLNM

      CHECKPOINT;

But it is not working correctly

Error:
"Incorrect syntax near the keyword 'AS'.".
Also would like to know if this:
'IF OBJECT_ID  (''dbo.' + @TBLNM + ''') IS NOT NULL  
Takes care of both Constaint & Table Existing

Please help and thanks...
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Amour22015

ASKER
That looks good,

I just have a few more question?

What does this do:
exec sp_executesql @sql;
I can not find sp_executesql
Or
Should I just change it to:
EXEC @sql

also does this:
'IF OBJECT_ID  (''dbo.' + @TBLNM + ''') IS NOT NULL  
Takes care of both Constaint & Table Existing


I could not tell because there are other errors with the SP, but they have nothing to do with this post.

Thanks
Scott Pletcher

>> What does this do:
exec sp_executesql @sql; <<

It executes dynamic SQL.  It is fully documented in Books Online; do a Google search for:
SQL Server sp_executesql


>>Should I just change it to:
EXEC @sql <<

No.  sp_executesql is preferred, because it makes it much harder to do SQL injection.


>> also does this:
'IF OBJECT_ID  (''dbo.' + @TBLNM + ''') IS NOT NULL  
Takes care of both Constaint & Table Existing <<

No.  It only checks to see if the table exists.
Amour22015

ASKER
Ok,

I would like to first check if a Constaint exist, if it does delete and the table.

I need to add this to the @sql

please help and thanks
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Amour22015

ASKER
Thanks
Scott Pletcher

SELECT @sql = 'IF OBJECT_ID (''dbo.' + @TBLNM + ''') IS NOT NULL AND OBJECT_ID (''' + 'CK_' + @TBLNM + '_Max_File_ID'') IS NOT NULL ' +