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
Microsoft SQL ServerMicrosoft SQL Server 2008SSISSQL

Avatar of undefined
Last Comment
Amour22015

8/22/2022 - Mon
Ganapathi

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?
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
Amour22015

ASKER
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
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
ASKER CERTIFIED SOLUTION
Ganapathi

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
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
Amour22015

ASKER
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
Amour22015

ASKER
Also,

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

thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Amour22015

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

Thanks
Amour22015

ASKER
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
Amour22015

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Amour22015

ASKER
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
Amour22015

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