Link to home
Start Free TrialLog in
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...
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Amour22015
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
>> 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.
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
Thanks
SELECT @sql = 'IF OBJECT_ID (''dbo.' + @TBLNM + ''') IS NOT NULL AND OBJECT_ID (''' + 'CK_' + @TBLNM + '_Max_File_ID'') IS NOT NULL ' +