带参数存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

ALTER PROCEDURE [dbo].[Save_Note_New]
    @note_id int output,
    @master_id nvarchar(max),
    @master_table nvarchar(50),
    @note_type nvarchar(20),
    @note nvarchar(max),
    @add_date datetime,
    @authorpersonid int,
    @note_wh nvarchar(50)
   
AS
  declare @signname nvarchar(50)
BEGIN
  BEGIN TRANSACTION tran_note_new
  Save Transaction tran_note_new

  select @signname=last_name+' '+first_name from person where person_id=@authorpersonid
  if @note_id=0
    begin
      insert into [note] (master_id,master_table_name,note_content,note_type,sign_name,author_id,add_date,note)
            values(@master_id,@master_table,@note,@note_type,@signname,@authorpersonid,@add_date,@note_wh)
      select @note_id=(@@identity)
    end
  else
    begin
      update [note] set note_content=@note,sign_name=@signname,author_id=@authorpersonid,update_date=getdate(),add_date=@add_date,note=@note_wh where note_id=@note_id
    end
   
IF ( @@ERROR <> 0 )
  BEGIN 
     ROLLBACK TRANSACTION tran_note_new
  END
ELSE
  BEGIN
    COMMIT TRANSACTION tran_note_new
  END    
IF ( @@TRANCOUNT > 0 )
  BEGIN
    ROLLBACK TRANSACTION tran_note_new
  END
select @note_id  
END