Stored Procedure Help

[shivashankara]

nenu konni stored procedures rayali
nenu rasindi correct ohh kadooo chepandi plz

[b]1st Query:[/b]

--- 12/15/2005 - Changed to rolling 180 days per conversation with Jim Crain. POS is using these audit tables to push information out to stores.
--- 11/20/2006 - Changed to rolling 60 days per conversation with Jim Crain. POS is using these audit tables to help performance
DECLARE @dtmRollingDate datetime
SELECT @dtmRollingDate = DATEADD(day, -30, CONVERT(varchar(10), GETDATE(), 101))
DELETE
FROM Audit_Vehicle_Service_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Visit_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Promo_Redeemed_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Rule_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Mail_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Customer_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

[b]1st Query Answer:[/b]

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'usp_Vehicleaudittable'
)
DROP PROCEDURE dbo.usp_Vehicleaudittable
GO
CREATE PROCEDURE [email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]dbo.usp_Vehicleaudittable[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]--@param1[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]AS[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]BEGIN[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]DECLARE[/email] @ERRORMSG VARCHAR(5000)
SET @ERRORMSG=NULL;

SET XACT_ABORT ON
BEGIN TRY

BEGIN TRAN
--- 12/15/2005 - Changed to rolling 180 days per conversation with Jim Crain. POS is using these audit tables to push information out to stores.
--- 11/20/2006 - Changed to rolling 60 days per conversation with Jim Crain. POS is using these audit tables to help performance
DECLARE @dtmRollingDate datetime
SELECT @dtmRollingDate = DATEADD(day, -30, CONVERT(varchar(10), GETDATE(), 101))
DELETE
FROM Audit_Vehicle_Service_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Visit_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Promo_Redeemed_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Rule_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Mail_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Customer_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
COMMIT
RETURN 0
END TRAN
END TRY

BEGIN CATCH
ROLLBACK TRAN
SET @ErrorMsg = ERROR_MESSAGE() + ' Transaction has been rolled back. '
RAISERROR(@ErrorMsg, 16, 1)
RETURN 1

END CATCH
GO
END

GO

[shivashankara]

paina above query ki nenu kinda answer lo stored procedure rasina
anduloo em aina wrong untay plz chepandi...help me out asap.

[ghazni]

anni deletes okay sp lo rastunava.................

[shivashankara]

[quote name='ghazni' timestamp='1345477869' post='1302341860']
anni deletes okay sp lo rastunava.................
[/quote]

yaa, ala rayodaaa?

[ghazni]

avanni different tables aitey joins cheyalema....... not sure kani just asking.........

[ChampakDas]

Here are my suggestions:

1. Use BEGIN TRAN before TRY CATCH block.
2. Use debug or print statements after each delete statement.
3. Create error message and use it in catch block and raise an error with no wait.

DECLARE @vERROR_MESSAGE NVARCHAR(MAX)
SELECT @vERROR_MESSAGE = ERROR_MESSAGE();
RAISEERRROR('%s', 16, 2, @vERROR_MESSAGE) WITH NOWAIT

Check the number above I dont remember it correctly.

4. Always use dbo.Tablename instead of Tablename, performance is some pico seconds faster than the latter.

Thats all I have.

[ChampakDas]

that is wrong.
[quote name='shivashankara' timestamp='1345477557' post='1302341840']
nenu konni stored procedures rayali
nenu rasindi correct ohh kadooo chepandi plz

[b]1st Query:[/b]

--- 12/15/2005 - Changed to rolling 180 days per conversation with Jim Crain. POS is using these audit tables to push information out to stores.
--- 11/20/2006 - Changed to rolling 60 days per conversation with Jim Crain. POS is using these audit tables to help performance
DECLARE @dtmRollingDate datetime
SELECT @dtmRollingDate = DATEADD(day, -30, CONVERT(varchar(10), GETDATE(), 101))
DELETE
FROM Audit_Vehicle_Service_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Visit_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Promo_Redeemed_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Rule_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Mail_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Customer_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

[b]1st Query Answer:[/b]

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'usp_Vehicleaudittable'
)
DROP PROCEDURE dbo.usp_Vehicleaudittable
GO
CREATE PROCEDURE [email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]dbo.usp_Vehicleaudittable[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]--@param1[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]AS[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]BEGIN[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]DECLARE[/email] @ERRORMSG VARCHAR(5000)
SET @ERRORMSG=NULL;

SET XACT_ABORT ON
BEGIN TRY

BEGIN TRAN
--- 12/15/2005 - Changed to rolling 180 days per conversation with Jim Crain. POS is using these audit tables to push information out to stores.
--- 11/20/2006 - Changed to rolling 60 days per conversation with Jim Crain. POS is using these audit tables to help performance
DECLARE @dtmRollingDate datetime
SELECT @dtmRollingDate = DATEADD(day, -30, CONVERT(varchar(10), GETDATE(), 101))
DELETE
FROM Audit_Vehicle_Service_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Visit_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Promo_Redeemed_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
DELETE
FROM Audit_Vehicle_Rule_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Mail_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

DELETE
FROM Audit_Vehicle_Customer_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
COMMIT
RETURN 0
[color=#ff0000][size=5][b]END TRAN[/b][/size][/color]
END TRY

BEGIN CATCH
ROLLBACK TRAN
SET @ErrorMsg = ERROR_MESSAGE() + ' Transaction has been rolled back. '
RAISERROR(@ErrorMsg, 16, 1)
RETURN 1

END CATCH
GO
END

GO
[/quote]

[shivashankara]

so,
do u want me to make below changes in above sp?

1. Use BEGIN TRAN before TRY CATCH block.
2. Use debug or print statements after each delete statement.
3. Create error message and use it in catch block and raise an error with no wait.

DECLARE @vERROR_MESSAGE NVARCHAR(MAX)
SELECT @vERROR_MESSAGE = ERROR_MESSAGE();
RAISEERRROR('%s', 16, 2, @vERROR_MESSAGE) WITH NOWAIT

Check the number above I dont remember it correctly.

4. Always use dbo.Tablename instead of Tablename, performance is some pico seconds faster than the latter.

[shivashankara]

so, after every delete statement
do u want me to add

PRINT @dtmRollingDate;

[shivashankara]

ltt

[ChampakDas]

yes, its a recommendation. It's up to you.
[quote name='shivashankara' timestamp='1345479090' post='1302341949']
so,
do u want me to make below changes in above sp?

1. Use BEGIN TRAN before TRY CATCH block.
2. Use debug or print statements after each delete statement.
3. Create error message and use it in catch block and raise an error with no wait.

DECLARE @vERROR_MESSAGE NVARCHAR(MAX)
SELECT @vERROR_MESSAGE = ERROR_MESSAGE();
RAISEERRROR('%s', 16, 2, @vERROR_MESSAGE) WITH NOWAIT

Check the number above I dont remember it correctly.

4. Always use dbo.Tablename instead of Tablename, performance is some pico seconds faster than the latter.
[/quote]

ya print statements pettu...to know how many records are deleted..
[quote name='shivashankara' timestamp='1345479263' post='1302341968']
so, after every delete statement
do u want me to add

PRINT @dtmRollingDate;
[/quote]

[shivashankara]

this is what i did

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'usp_Vehicleaudittable'
)
DROP PROCEDURE dbo.usp_Vehicleaudittable
GO
CREATE PROCEDURE [email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]dbo.usp_Vehicleaudittable[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]--@param1[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]AS[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]BEGIN[/email]
[email="dbo.usp_Vehicleaudittable--@param1ASBEGINDECLARE"]DECLARE[/email] @ERRORMSG VARCHAR(5000)
SET @ERRORMSG=NULL;

SET XACT_ABORT ON
BEGIN TRY

--- 12/15/2005 - Changed to rolling 180 days per conversation with Jim Crain. POS is using these audit tables to push information out to stores.
--- 11/20/2006 - Changed to rolling 60 days per conversation with Jim Crain. POS is using these audit tables to help performance
DECLARE @dtmRollingDate datetime
SELECT @dtmRollingDate = DATEADD(day, -30, CONVERT(varchar(10), GETDATE(), 101))
DELETE
FROM Audit_Vehicle_Service_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
PRINT @dtmRollingDate;
DELETE
FROM Audit_Vehicle_Visit_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
PRINT @dtmRollingDate;

DELETE
FROM Audit_Vehicle_Promo_Redeemed_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

PRINT @dtmRollingDate;
DELETE
FROM Audit_Vehicle_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
PRINT @dtmRollingDate;

DELETE
FROM Audit_Vehicle_Rule_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
PRINT @dtmRollingDate;
DELETE
FROM Audit_Vehicle_Mail_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2
PRINT @dtmRollingDate;
DELETE
FROM Audit_Vehicle_Customer_Tb
WHERE Audit_Insert_Date < @dtmRollingDate AND
Process_Status_Flag = 2

PRINT @dtmRollingDate;
COMMIT
RETURN 0

END TRY

BEGIN CATCH
ROLLBACK TRAN
SET @ErrorMsg = ERROR_MESSAGE() + ' Transaction has been rolled back. '
RAISERROR(@ErrorMsg, 16, 1)
RETURN 1

END CATCH
GO
END
GO

I got these errors, when i parsed sp

Msg 170, Level 15, State 1, Procedure usp_Vehicleaudittable, Line 16
Line 16: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Procedure usp_Vehicleaudittable, Line 82
Line 82: Incorrect syntax near 'TRY'.
Msg 195, Level 15, State 10, Procedure usp_Vehicleaudittable, Line 88
'ERROR_MESSAGE' is not a recognized function name.
Msg 156, Level 15, State 1, Procedure usp_Vehicleaudittable, Line 93
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'END'.

[ChampakDas]

dude, nuvu asalu transaction start aa cheyatledhu rollback petesavu...try block pina pettu ante mothaniki athipadesav kada...

[img]http://s5.tinypic.com/1zzgs3b_th.jpg[/img]

[jbourne]

[img]http://s5.tinypic.com/1zzgs3b_th.jpg[/img]


db lo sarswati putrulu

[shivashankara]

[quote name='ChampakDas' timestamp='1345481016' post='1302342155']
dude, nuvu asalu transaction start aa cheyatledhu rollback petesavu...try block pina pettu ante mothaniki athipadesav kada...

[img]http://s5.tinypic.com/1zzgs3b_th.jpg[/img]
[/quote]

ohh ok transaction peduta bhayya.