存储过程模板代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】存储过程模板

作者:Dezai.CN / 发布于2012/7/9/ 575

存储过程模板




create/alter procedure <SP Name>

/*---------------------------------------------------------------------------
DESCRIPTION:
This procedure will get the list of users.

AUTHOR:
DATE:

PARAMETERS:
Parameter 1: Description of parameter1
Parameter 2: Description of parameter2

RETURN VALUE:

AFFECTED TABLES:
Table1
Table2
---------------------------------------------------------------------------*/

@DiscussionID uniqueidentifier
...
as

begin

-- other declare statements

declare @Error int

declare @TrCount tinyint



-- other set statements

set @Error = 0

set @TrCount = @@trancount



-- other core/main logic Example1...

select @Error = @@error, @RowCount = @@rowcount

if (@Error <> 0) goto error



-- other core/main logic Example 2...

if (@RowCount = 0)

begin set @Error = <some sysmessage error#> raiserror (@Error, 16, 1) goto error end



-- other core/main logic Example 3...

if @Error <> 0

begin raiserror(@Error, 16, 1) goto error end



-- should be last statements after processing all other logic

if @@trancount > @TrCount

commit transaction

goto conclude



-- other logic...



error:

if @@trancount > @TrCount

rollback transaction



conclude:

-- anything that needs to be undone like cleanup, drop temp tables, etc.

-- set flags need to be reset, for example 'set nocount off'

-- prepare output parameters if any

-- other xml output for return if any, for example &lsquo;select @Error RetCode for xml raw&rsquo;

return @Error

end

go

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　存储过程模板　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)