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...
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Scott Pletcher
ASKER CERTIFIED SOLUTION
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
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
>> 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
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
Thanks
SELECT @sql = 'IF OBJECT_ID (''dbo.' + @TBLNM + ''') IS NOT NULL AND OBJECT_ID (''' + 'CK_' + @TBLNM + '_Max_File_ID'') IS NOT NULL ' +
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