Stored Procedure Help

[shivashankara]

[quote name='jbourne' timestamp='1345481174' post='1302342165']
[img]http://s5.tinypic.com/1zzgs3b_th.jpg[/img]


db lo sarswati putrulu
[/quote]


telvadu bhayya...em cheyali

[gundugadu]

try this:


SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [db]
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Updating The DataBase'
GO


----enter your code



IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

[ChampakDas]

BEGIN TRAN
BEGIN TRY

COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH

Print statement antava this is what I am talking

PRINT 'No of deleted records from table xyz' + CONVERT(VARCHAR(10), @vDeletedRecords) + '.'

what is @vDeletedRecords

put the below statement right after the delete statement

SELECT @vDeletedRecords = @@ROWCOUNT

@@ROWCOUNT will give how many records were used for the transaction.
[quote name='shivashankara' timestamp='1345481695' post='1302342214']
ohh ok transaction peduta bhayya.
[/quote]

[ChampakDas]

[img]http://lh6.ggpht.com/-6CXuzXaFDhI/T-eKjdyhm4I/AAAAAAAAHCE/BAl7bcbBreQ/s150/PK-95.gif[/img]
[quote name='jbourne' timestamp='1345481174' post='1302342165']
[img]http://s5.tinypic.com/1zzgs3b_th.jpg[/img]


db lo sarswati putrulu
[/quote]

[ghazni]

gp.....

[ChampakDas]

LTT in case my answers dint help

[shivashankara]

trying to fix this error

Msg 195, Level 15, State 10, Procedure usp_Vehicleaudittable, Line 48
'ERROR_MESSAGE' is not a recognized function name.

[dotnetrockz]

[quote name='shivashankara' timestamp='1345487723' post='1302343187']
trying to fix this error

Msg 195, Level 15, State 10, Procedure usp_Vehicleaudittable, Line 48
'ERROR_MESSAGE' is not a recognized function name.
[/quote]
write this code in catch block..


[size="2"][color="#0000ff"][size="2"][color="#0000ff"]Begin[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]Catch[/color][/size][/color][/size][size="2"] [/size]
[size="2"][color="#0000ff"][size="2"][color="#0000ff"]Declare[/color][/size][/color][/size][size="2"] @err_msg [/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]Varchar[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"]5000[/size][size="2"][color="#808080"][size="2"][color="#808080"])[/color][/size][/color][/size]
[size="2"][color="#0000ff"][size="2"][color="#0000ff"]SELECT[/color][/size][/color][/size][size="2"][color="#000000"] @Err_msg [/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]=[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]Substring[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#ff0000"][size="2"][color="#ff0000"]N'Error '[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]CONVERT[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]varchar[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#000000"]50[/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]),[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ERROR_NUMBER[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]())[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size]
[size="2"][color="#ff0000"][size="2"][color="#ff0000"]', Severity '[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]CONVERT[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]varchar[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"]5[/size][size="2"][color="#808080"][size="2"][color="#808080"]),[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ERROR_SEVERITY[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]())[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size]
[size="2"][color="#ff0000"][size="2"][color="#ff0000"]', State '[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]CONVERT[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]varchar[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"]5[/size][size="2"][color="#808080"][size="2"][color="#808080"]),[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ERROR_STATE[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]())[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size]
[size="2"][color="#ff0000"][size="2"][color="#ff0000"]', Procedure '[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ISNULL[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ERROR_PROCEDURE[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"](),[/color][/size][/color][/size] [size="2"][color="#ff0000"][size="2"][color="#ff0000"]'-'[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"])[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size]
[size="2"][color="#ff0000"][size="2"][color="#ff0000"]', Line '[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]CONVERT[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]varchar[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"]5[/size][size="2"][color="#808080"][size="2"][color="#808080"]),[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ERROR_LINE[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]())[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size]
[size="2"][color="#ff0000"][size="2"][color="#ff0000"]', Error Message: '[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]+[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]ERROR_MESSAGE[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"](),[/color][/size][/color][/size][size="2"]1[/size][size="2"][color="#808080"][size="2"][color="#808080"],[/color][/size][/color][/size][size="2"]3000[/size][size="2"][color="#808080"][size="2"][color="#808080"]);[/color][/size][/color][/size]

[size="2"][color="#0000ff"][size="2"][color="#0000ff"]Print[/color][/size][/color][/size][size="2"] @Err_msg [/size]
[size="2"][color="#0000ff"][size="2"][color="#0000ff"]IF[/color][/size][/color][/size] [size="2"][color="#ff00ff"][size="2"][color="#ff00ff"]@@TRANCOUNT[/color][/size][/color][/size] [size="2"][color="#808080"][size="2"][color="#808080"]>[/color][/size][/color][/size][size="2"] 0 [/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]ROLLBACK[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"];[/color][/size][/color][/size]

[size="2"][color="#0000ff"][size="2"][color="#0000ff"]RAISERROR [/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"]([/color][/size][/color][/size][size="2"]@err_msg[/size][size="2"][color="#808080"][size="2"][color="#808080"],[/color][/size][/color][/size][size="2"]16[/size][size="2"][color="#808080"][size="2"][color="#808080"],[/color][/size][/color][/size][size="2"]1[/size][size="2"][color="#808080"][size="2"][color="#808080"])[/color][/size][/color][/size] [size="2"][color="#0000ff"][size="2"][color="#0000ff"]with[/color][/size][/color][/size] [size="2"][color="#0000ff"][size="2"][color="#0000ff"]seterror[/color][/size][/color][/size]
[size="2"][color="#0000ff"][size="2"][color="#0000ff"]End[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]Catch[/color][/size][/color][/size]
[size="2"][color="#0000ff"][size="2"][color="#0000ff"]SET[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]NOCOUNT[/color][/size][/color][/size][size="2"][color="#000000"] [/color][/size][size="2"][color="#0000ff"][size="2"][color="#0000ff"]OFF[/color][/size][/color][/size]
[size="2"][color="#0000ff"][size="2"][color="#0000ff"]RETURN[/color][/size][/color][/size][size="2"][color="#808080"][size="2"][color="#808080"];[/color][/size][/color][/size][size="2"] [/size]

[shivashankara]

nenu ee sp 2000 lo parse chestunna
but 2000 lo try, catch undavu kada
andukay errors vastunayi

but this will execute in 2008 anukunta without errors.