February 26, 2013 at 8:30 am
Greetings -
I have a database which contains over 70 tables, there are over 30 tables that I need to delete rows from them based on two parms I am passing to the stored procedure.
Below is the stored procedure, but I know it got to be a better way to delete rows from all tables in the database than wtiting single delete statment for each table.....
Thank you.
***************************************
ALTER PROCEDURE [dbo].[DELETEINC_SP]
@incId int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @injId int
DECLARE @trtid int
SELECT @injId = Inj_id FROM Inj_T WHERE Inc_ID = @incId
If @@error <> 0 goto ERR_HANDLER
SELECT @trtid = IT_ID FROM IT_T WHERE Inj_id = @injId
DELETE FROM ITr_T WHERE ITr_ID = @trtid
If @@error <> 0 goto ERR_HANDLER
DELETE FROM INo_T WHERE Inj_id = @injId
If @@error <> 0 goto ERR_HANDLER
DELETE FROM IA_T WHERE Inj_id = @injId
If @@error <> 0 goto ERR_HANDLER
DELETE FROM IN_T WHERE Inj_id = @injId
IF @@ERROR <> 0 goto ERR_HANDLER
DELETE FROM IW_T WHERE Inc_ID = @incId
If @@error <> 0 goto ERR_HANDLER
DELETE FROM SUP_T WHERE Inc_ID = @incId
If @@error <> 0 goto ERR_HANDLER
DELETE FROM LOC_T WHERE Inc_ID = @incId
If @@error <> 0 goto ERR_HANDLER
DELETE FROM EQU_T WHERE Inc_ID = @incId
If @@error <> 0 goto ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
ERR_HANDLER:
Select 'Unexpected error occurred!'
ROLLBACK TRANSACTION
RETURN 1
END
February 26, 2013 at 8:36 am
if you use SET XACT_ABORT ON, you can make the code a little easier to read; if any error occurs, it stops the process and rollsback the transaction automatically:
each delete must have it's own seperate delete statement...so just make this 70 delete statements, but a bit easier to read:
ALTER PROCEDURE [dbo].[DELETEINC_SP]
@incId int
AS
BEGIN
SET NOCOUNT ON;
--if any error occurs, roll back everything and return a non zero error code.
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @injId int
DECLARE @trtid int
SELECT @injId = Inj_id FROM Inj_T WHERE Inc_ID = @incId
SELECT @trtid = IT_ID FROM IT_T WHERE Inj_id = @injId
DELETE FROM ITr_T WHERE ITr_ID = @trtid
DELETE FROM INo_T WHERE Inj_id = @injId
DELETE FROM IA_T WHERE Inj_id = @injId
DELETE FROM IN_T WHERE Inj_id = @injId
DELETE FROM IW_T WHERE Inc_ID = @incId
DELETE FROM SUP_T WHERE Inc_ID = @incId
DELETE FROM LOC_T WHERE Inc_ID = @incId
DELETE FROM EQU_T WHERE Inc_ID = @incId
COMMIT TRANSACTION
END --PROC
Lowell
February 26, 2013 at 8:48 am
Thank you... Is there a way you can use cursor or foreachtable to see if that inc or inj fields are defined and if so based on the value to delete either one?
February 26, 2013 at 9:01 am
possible, but i wouldn't recommend it; deletes should be decided based on some analysis, and not just because a table might contain a columname that matches or something. Additionally, you lose a lot of error validation and checking if you use a cursor.
it might be that some values need to be set to null, instead of the row containing the value being deleted.
Additionally, one of the deletes could fail due to FK violations. that's why i'd recommend an analysis instead.
Anyway,the cursor would need to find all the tables that contain the columns, i guess
something like this would be the basis of it.
DECLARE
@isql VARCHAR(2000),
@tbname VARCHAR(64),
@colname VARCHAR(64)
DECLARE c1 CURSOR FOR
SELECT
tabz.name AS TableName,
colz.name AS ColumnName
FROM sys.tables tabz
INNER JOIN sys.columns colz
ON tabz.object_id = colz.object_id
WHERE colz..name IN('Inc_ID','Inj_id','ITr_ID')
OPEN c1
FETCH NEXT FROM c1 INTO @tbname,@colname
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql = 'DELETE FROM '
+ QUOTENAME(@tbname)
+ 'WHERE '
+ QUOTENAME(@colname)
+ ' = '
+ CASE
WHEN @colname = 'Inc_ID' THEN CONVERT(VARCHAR, @incId)
WHEN @colname = 'Inj_id' THEN CONVERT(VARCHAR, @injId)
WHEN @colname = 'ITr_ID' THEN CONVERT(VARCHAR, @trtid)
END
PRINT @isql
EXEC(@isql)
FETCH NEXT FROM c1 INTO @tbname,@colname
END
CLOSE c1
DEALLOCATE c1
Lowell
February 26, 2013 at 9:03 am
Many thanks to you, I will test it and see if it works...
Lava
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply