欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql server存储过程,常用的格式

程序员文章站 2022-07-05 16:57:55
BEGINSET NOCOUNT ON;if @_MODE NOT IN ('A','M','D') begin raiserror('参数错误!',16,3); return; end; declare @rowcount int,@error int; if @_MODE='A'begin in ......

BEGIN
SET NOCOUNT ON;
if @_MODE NOT IN ('A','M','D')
begin
  raiserror('参数错误!',16,3);
  return;
end;

declare @rowcount int,@error int;

if @_MODE='A'
begin
  insert into szdxInfo (Id,Bh,[Name],nation) select
    @Id,@Bh,@Name,
    @Nation;
  if @@error<>0 return;
end;

if @_MODE='M'
begin
  update szdxInfo set [Name]=@Name,
    nation=@Nation
    where id=@id;
  select @error=@@error,@rowcount=@@rowcount;
  if @error<>0 return;
  if @rowcount<>1
    begin
      raiserror('没有修改记录!!',16,3)
      return;
    end;
end;

if @_MODE='D'
begin
  delete szdxInfo where Id=@id;
  select @error=@@error,@rowcount=@@rowcount;
  if @error<>0 return;
  if @rowcount<>1
    begin
    raiserror('没有删除记录!!',16,3)
    return;
  end;
end;

insert into ActionLog ([date],[userid],Computerid,
  TableName,[Action]) select
    getdate(),@_USERID,
    @_COMPUTERID,
    'szdxInfo',@_MODE;

/*
exec Update_szdxInfo @id='11',
@Name ='刘某某',
@Nation ='汉族',
@_MODE ='A',
@_USERID=1,
@_COMPUTERID=1
select * from szdxINfo
*/
END