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