data deletion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data deletion

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-23 : 06:27:00

hello experts ,
I created one SP for data deletion
I just wanted to check weather i'm deleting records matching to my query only,please check below code for this

also if you could suggests any better idea for deletion and performance improvement.

create Procedure [dbo].[DataPurge](@LastRunDate as varchar(100))
AS
begin

SET NOCOUNT ON;

BEGIN TRY
--BEGIN TRAN
delete from dbo.[PROD_SUM] where exists
(
SELECT * FROM dbo.[PROD_SUM] where [PROD_SUM_MES_PRODU_DATE_DT] <= @LastRunDate
);
--END TRAN
END TRY

BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK --TRAN
-- Raise an error with the details of the exception
DECLARE @ErrMsg1 nvarchar(4000),
@ErrSeverity1 int,
@ErrorState1 INT
SELECT @ErrMsg1 = ERROR_MESSAGE(),
@ErrSeverity1 = ERROR_SEVERITY(),
@ErrorState1 = ERROR_STATE()

RAISERROR(@ErrMsg1, @ErrSeverity1, @ErrorState1)

END CATCH

---------------------------------------------------------------------------------------------

BEGIN TRY
--BEGIN TRAN
Delete from dbo.[PROD_WASTE] where exists
(
SELECT * FROM dbo.[PROD_WASTE] where [PROD_WASTE_MES_PRODU_DATE_D] <= @LastRunDate--getdate()
)
--END TRAN
END TRY

BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK --TRAN
-- Raise an error with the details of the exception
DECLARE @ErrMsg2 nvarchar(4000),
@ErrSeverity2 int,
@ErrorState2 INT
SELECT @ErrMsg2 = ERROR_MESSAGE(),
@ErrSeverity2 = ERROR_SEVERITY(),
@ErrorState2 = ERROR_STATE()

RAISERROR(@ErrMsg2, @ErrSeverity2, @ErrorState2)
END CATCH
---------------------------------------------
---------------------------------------------------------------------------------------------
BEGIN TRY
--BEGIN TRAN
delete from dbo.[WORK_HIST_DETL] where exists
(SELECT
[WORK_HIST_DETL_ACTIV_TEXT_C]
,[WORK_HIST_DETL_ANALY_ACTIV_]
,[WORK_HIST_DETL_ANALY_CAUSE_]
,[WORK_HIST_DETL_ANALY_DAMAG_]
,[WORK_HIST_DETL_ANALY_OBJEC_]
,[WORK_HIST_DETL_CAUSE_TEXT_C]
,[WORK_HIST_DETL_DAMAG_TEXT_C]
,[WORK_HIST_DETL_EQUIP_ID_CHR]
,[WORK_HIST_DETL_FUNCT_LOCAT_]
,[WORK_HIST_DETL_ITEM_NUMBE_C]
,[WORK_HIST_DETL_NOTIF_ID_CHR]
,[WORK_HIST_DETL_ORDER_ID_CHR]
,[WORK_HIST_DETL_SAP_ACTIV_CH]
,[WORK_HIST_DETL_SAP_ACTIV_CO]
,[WORK_HIST_DETL_SAP_ACTIV_DE]
,[WORK_HIST_DETL_SAP_CAUSE_CH]
,[WORK_HIST_DETL_SAP_CAUSE_CO]
,[WORK_HIST_DETL_SAP_CAUSE_DE]
,[WORK_HIST_DETL_SAP_DAMAG_CH]
,[WORK_HIST_DETL_SAP_DAMAG_CO]
,[WORK_HIST_DETL_SAP_DAMAG_DE]
,[WORK_HIST_DETL_SAP_OBJEC_PA]
,[WORK_HIST_DETL_SAP_OBJPT_C]
,[WORK_HIST_DETL_SAP_OBJPT_D]
,[WORK_HIST_DETL_WORK_HISTO_I]
,[WORK_HIST_DETL_INTER_SAP_FU]
FROM dbo.[WORK_HIST_DETL] left join dbo.[WORK_HISTORY]
on [WORK_HIST_DETL].[WORK_HIST_DETL_WORK_HISTO_I]= [WORK_HISTORY].[WORK_HISTORY_WORK_HISTO_ID_]
Where [WORK_HIST_DETL].[WORK_HIST_DETL_WORK_HISTO_I] <> [WORK_HISTORY].[WORK_HISTORY_WORK_HISTO_ID_]
)
--END TRAN
END TRY

BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK--TRAN
-- Raise an error with the details of the exception
DECLARE @ErrMsg17 nvarchar(4000),
@ErrSeverity17 int,
@ErrorState17 INT
SELECT @ErrMsg17 = ERROR_MESSAGE(),
@ErrSeverity17 = ERROR_SEVERITY(),
@ErrorState17 = ERROR_STATE()

RAISERROR(@ErrMsg17, @ErrSeverity17, @ErrorState17)
END CATCH
-----------------------------------------------
SET NOCOUNT OFF;
-----------I am taking only 3 tables here but in all there are 20 tables ------------
end

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-23 : 07:25:41

i m using sql server2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 09:58:09

I dont think anybody will take the pain of going through all this. It would be better if you post problem part alone with some sample data and then explain what you actually expect out of it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-23 : 10:38:49

posted what exactly i want to explain.........

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-24 : 00:57:54

guys please check

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-24 : 02:39:15

experts pls help

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 03:32:34

"I just wanted to check weather i'm deleting records matching to my query only"

No, your code will delete the whole table (if the EXISTS condition is true)

It would help if you formatted your code so we could read it.

Do not bump your threads. We get to read the posts when we have time, we don;t need you hassling us. If you have an urgent problem then pay for a consultant; this is a free service based on people's goodwill, if you abuse that then you will probably find that people here won't bother to help you. Same if you can't be bothered to format your code.

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-24 : 03:37:12

ok ..i formatted code and removed unrequired statements....

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-24 : 03:42:07

ok also I'm sure where is the transcation starting and ending?

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 04:39:28

"i formatted code and removed unrequired statements."

Sorry, I mean to format it so we could read it easily - e.g. using the [CODE] tag - see the FORUM CODE link to the left of the Reply box.

"I'm sure where is the transcation starting and ending?"

If you use BEGIN TRANSACTION and either COMMIT or ROLLBACK that defines a transaction. Otherwise the transaction is implicit (and for the whole statement block)

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-24 : 05:30:48

and if in case any error it will be catched in CATCH block right?
and it will rollback that transcation ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 11:31:31

quote:
Originally posted by qutesanju

and if in case any error it will be catched in CATCH block right?
and it will rollback that transcation ?

not all errors wont be caught by CATCH block
Errors that have a severity of 20 or higher and also 10 or lower are not handled by CATCH block

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-30 : 02:29:26

I mean to say .......
if TRY i'm deleting data,so in in case there would be error in data deletion then it would be catched into CATCH block and transaction would be rolled back? right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:07:48

quote:
Originally posted by qutesanju

I mean to say .......
if TRY i'm deleting data,so in in case there would be error in data deletion then it would be catched into CATCH block and transaction would be rolled back? right?

what error are you trying to capture via catch?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-31 : 01:27:50

if in case erroe occured at the SP execution

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 03:50:33

To repeat what Visakh said:

"Errors that have a severity of 20 or higher and also 10 or lower are not handled by CATCH block"

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-31 : 05:56:07

so in all should I replace my CATCH block by any other alternatives?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-31 : 06:28:35

Try something similar to this...

CREATE PROCEDURE	dbo.DataPurge
(
	@LastRunDate DATETIME
)
AS

SET NOCOUNT ON

DECLARE @ErrMsg NVARCHAR(4000), 
	@ErrSeverity INT,
	@ErrorState INT

BEGIN TRY
	BEGIN TRAN

	DELETE
	FROM	dbo.[PROD_SUM]
	WHERE	[PROD_SUM_MES_PRODU_DATE_DT] <= @LastRunDate 

	DELETE
	FROM	dbo.[PROD_WASTE]
	WHERE	[PROD_WASTE_MES_PRODU_DATE_D] <= @LastRunDate

	DELETE		w
	FROM		wdbo.[WORK_HIST_DETL] AS w
	LEFT JOIN	dbo.[WORK_HISTORY] AS q ON q.[WORK_HISTORY_WORK_HISTO_ID_] = [WORK_HIST_DETL_WORK_HISTO_I]
	WHERE		[WORK_HISTORY_WORK_HISTO_ID_] IS NULL

	COMMIT TRAN
END TRY

BEGIN CATCH
		SELECT	@ErrMsg = ERROR_MESSAGE(),
			@ErrSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE()

		ROLLBACK TRAN

		RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState)
END CATCH

N 56°04'39.26"
E 12°55'05.63"

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-31 : 07:01:49

great peso!!!
earlier i had repated try/catch for 19 times for 19 tables

also help me to understand you replied-->
DELETE w
FROM wdbo.[WORK_HIST_DETL] AS w
LEFT JOIN dbo.[WORK_HISTORY] AS q ON q.[WORK_HISTORY_WORK_HISTO_ID_] = [WORK_HIST_DETL_WORK_HISTO_I]
WHERE [WORK_HISTORY_WORK_HISTO_ID_] IS NULL

here wdbo means?

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-31 : 07:08:24

sorry it may b type O mistake ,I can understand

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-31 : 07:11:06

also in the code you posted PESO ,suupose in the table [PROD_SUM],it's having referrenced master table and [PROD_WASTE] has referrenced child table then will this SP be executed properly i mean will it delete data form above tables ?i.e.[PROD_SUM],[PROD_WASTE]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources