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