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
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
--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
ASKER
Also,
I hard coded the needed parameters inside of SP and recieved the same error message..
thanks
I hard coded the needed parameters inside of SP and recieved the same error message..
thanks
ASKER
Sorry did not mean to put all caps on post ID: 40035125 - Cap lock was on
Thanks
Thanks
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
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
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+'_Ma x_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
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+'_Ma
'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Did not resolve problem, but received no responses and need to move on with another post
After the SELECT statement, have an other statement to execute the query in @sql variable,
Add this after your SELECT statement
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?