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

Amour22015
Amour22015 used Ask the Experts™
on
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...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Change DELETE line as follows:

 ' DELETE FROM ' + @TBLNM + ' FROM ' + @TBLNM + ' AS T' +

Author

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
>> 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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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

Author

Commented:
Thanks
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial