Stored Procedure Help

August 20, 2012

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

August 20, 2012

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

August 20, 2012

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

August 20, 2012

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

yaa, ala rayodaaa?

August 20, 2012

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

August 20, 2012

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.

August 20, 2012

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]

August 20, 2012

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.

August 20, 2012

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

PRINT @dtmRollingDate;

August 20, 2012

ltt

August 20, 2012

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]

August 20, 2012

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

August 20, 2012

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]

August 20, 2012

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

db lo sarswati putrulu

August 20, 2012

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

Sign In

Stored Procedure Help

Recommended Posts

shivashankara

shivashankara

ghazni

shivashankara

ghazni

ChampakDas

ChampakDas

shivashankara

shivashankara

shivashankara

ChampakDas

shivashankara

ChampakDas

jbourne

shivashankara

Tell a friend

Most viewed in last 30 days

Activity