Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

SQL Server 2008 R2 - @sql = IF OBJECT_ID, ALTER TABLE, DROP TABLE

Hi experts I need your help with this,

I am very new to all this..

@TBLNM and @FILEID is a ResultSet parameter from SSIS that is "Foreach Loop Container" with a value/field in another table

      @fileId int,
      @TBLNM varchar (20),
Declare
            @sql nvarchar(4000)


I have this bit of code within a Stored Procedure and I know that it is not going to work:

select @sql = 'IF OBJECT_ID [dbo]. +('@TBLNM') IS NOT NULL' + 'ALTER TABLE [dbo].' + @TBLNM + ' DELETE CONSTRAINT [CK_' + @TBLNM + '_Max_File_ID] + ''
                  DROP TABLE [dbo].' + @TBLNM + '
                  FROM ' + @TBLNM + '
                  WHERE ' + @TBLNM + '.[FILE_ID] = ' + cast(@fileId as varchar(20))+ ' AND
                        EXISTS(SELECT #CLAIM_TEMP.CLM_KY
                                 FROM #CLAIM_TEMP
                                 WHERE #CLAIM_TEMP.CLM_KY = ' + @TBLNM + '.CLM_KY AND
                                    #CLAIM_TEMP.[FILE_ID] = ' + cast(@fileId as varchar(20)) + ')';


I want to be able to:
If table exit
Alter table constriants
Drop table
Where condition

I need to correct this code.

Please help and thanks
Avatar of Ganapathi
Ganapathi
Flag of India image

I could see that you are trying to do this as a dynamic query. You do not need to have a WHERE clause for this.

After the SELECT statement, have an other statement to execute the query in @sql variable,

Add this after your SELECT statement

EXEC @sql

Open in new window

Thats it.

The only thing I am not getting is the 4th one. After the DROP table you have included FROM clause. What are you trying to do there? Do you want to delete records from the table?
Avatar of Amour22015
Amour22015

ASKER

No I want to delete the entire table.

The Where statement is needed because

@FILEID has to match that of the file_id within the table that is goint to be deleted.

@TBLNM is the name of the table that is going to be deleted.

Thanks
You See,

All that is within the @sql is based on conditions

At first check to see if table exist:
If Object_id

Next
If @FILEID = @TBLNM .fileid then
Alter Table CONSTRAINT Delete it before deleting table (table will not delete unless you remove all CONSTRAINT)
Drop Table
EndIf

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Ganapathi
Ganapathi
Flag of India 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
Hi,

I am getting this error:
SSIS package "ClaimPurge.dtsx" starting.
Error: 0x0 at Purge Claims: Start Purge Of CLAIM - 2014-05-01 11:59:19
Error: 0x0 at Purge Claims: Incorrect syntax near the keyword 'DELETE'.
Error: 0xC002F210 at Purge Claims, Execute SQL Task: Executing the query "EXEC Purge_By_FileId ?,?,?,?" failed with the following error: "Incorrect syntax near ')'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0x0 at Purge Claims: Incorrect syntax near the keyword 'DELETE'.
Task failed: Purge Claims

This is related to the SP that we are working on, it is just that SSIS Executes "Purge_By_FileId"

Thanks
SO THIS IS WHAT I HAVE SO FAR:

--DELETE CLAIM TABLE
      PRINT 'Start DELETE Of CLAIM - ' + CONVERT(VARCHAR, GETDATE(), 120)
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
            SELECT @sql = 'IF OBJECT_ID (dbo.'+@TBLNM+') IS NOT NULL ' +
       'BEGIN '+
           'ALTER TABLE dbo.' + @TBLNM + ' DELETE CONSTRAINT CK_' + @TBLNM + '_Max_File_ID' +
           ' DELETE dbo.' + @TBLNM +
           ' FROM ' + @TBLNM + '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 'End DELETE of CLAIM - ' + CONVERT(VARCHAR, GETDATE(), 120)

THANKS
Also,

I hard coded the needed parameters inside of SP and recieved the same error message..

thanks
Sorry did not mean to put all caps on post ID: 40035125 - Cap lock was on

Thanks
Hi,

found that error:

It is not DELETE CONSTRAINT
It is Drop Constraint

But I have another error:
SSIS package "ClaimPurge.dtsx" starting.
Error: 0x0 at Purge Claims: Start DELETE Of CLAIM - 2014-05-01 13:27:21
Error: 0xC002F210 at Purge Claims, Execute SQL Task: Executing the query "EXEC Purge_By_FileId ?,?,?,?" failed with the following error: "Incorrect syntax near ')'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Purge Claims

Thanks
Ok,

I found that the "'BEGIN '" was what was giving the error above.

So I change it, also I needed to check for Contrainst exist:

Currently:
      BEGIN TRAN
            SELECT @sql =
            'IF OBJECT_ID(CK_'+@TBLNM+'_Max_File_ID' + ') IS NOT NULL' +
           'ALTER TABLE dbo.' + @TBLNM + ' DROP CONSTRAINT CK_' + @TBLNM + '_Max_File_ID' +
           'IF OBJECT_ID (dbo.'+@TBLNM+') IS NOT NULL ' +
           ' DELETE dbo.' + @TBLNM +
           ' FROM ' + @TBLNM + '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


But it again is having problems.

Please help thanks
SOLUTION
Avatar of Alpesh Patel
Alpesh Patel
Flag of India 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
Ok,
Declare
  @fileId int = 0,
  @TBLNM varchar (20) = 'DimAudit',
 
Do I need to declare, these 2 are parameters and get passed from a SSIS package?

So your are saying that this:
'IF OBJECT_ID  (''dbo.' + @TBLNM + ''') IS NOT NULL  

will take care of any constraint that are attached plus any tables existing?

also I get a error:
"Incorrect syntax near the keyword 'AS'.".

Please help and thanks
Did not resolve problem, but received no responses and need to move on with another post