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

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GanapathiFacets Developer

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

Author

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

Author

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

Facets Developer
Commented:
Use this and see if you get what you wanted

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 @sql

Open in new window

Author

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

Author

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

Author

Commented:
Also,

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

thanks

Author

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

Thanks

Author

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

Author

Commented:
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
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
Declare
  @fileId int = 0,
  @TBLNM varchar (20) = 'DimAudit',
  @sql nvarchar(4000)


SELECT @sql = 'IF OBJECT_ID  (''dbo.' + @TBLNM + ''') IS NOT NULL  ' +
       --'BEGIN '+
           '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 @sql

Author

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

Author

Commented:
Did not resolve problem, but received no responses and need to move on with another post

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