sqlserver 存儲過程 增加

CREATE PROCEDURE [dbo].[InsertMessage](
@strTable varchar(50),         --表名
@strValues nvarchar(1000),     --要插入的數據（用英文逗號分隔）,如果是字符串類型，需加單引號
@only_field varchar(20)=NULL,  --唯一性字段(列名)
@only_value varchar(20)=NULL,  --唯一性字段值
@msg nvarchar(50)=NULL         --錯誤消息
)
as
BEGIN SET NOCOUNT ON;
declare @sqlString varchar(5000);IF @only_field is not nullbegindeclare @p int;                    --查詢唯一性結果declare @sql nvarchar(1000);       --拼接查詢sql字串set @sql='SELECT @p=count(1) FROM ' +@strTable+' WHERE ' +@only_field+'='+@only_value;exec sp_executesql @sql,N'@p AS int OUTPUT',@p OUTPUTif @p > 0beginraiserror(@msg ,16,1)returnendelsebeginset @sqlString = 'insert into '+@strTable+' values ('+@strValues+')';exec(@sqlString);endend
ELSEbeginset @sqlString = 'insert into '+@strTable+' values ('+@strValues+')';exec(@sqlString);end
END--execute [dbo].[InsertMessage] 'base._Member',"0,2,getdate(),0,1,'4445','654321','654321',N'鄒柯00'",'AcctNbr','4445',N'會員卡號已存在,不能重復添加!'
--execute [dbo].[InsertMessage] 'base._Member',"0,2,getdate(),0,1,'4445','654321','654321',N'鄒柯00'",NULL,NULL,NULL

?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- 鄒柯
Create PROCEDURE CollegeAdd
(@username NVarChar(10),@cardid VarChar(30),@age int,@sex Char(1),@image_path  VarChar(50),@job_base_id int,@level_base_id int,@certificateid VarChar(15),@certificate_path VarChar(50),@BeginTime DateTime=null,@EndTime DateTime=null,@userid int out,@ceid int out
)
WITH ENCRYPTION
AS
BEGINSET NOCOUNT     ON;SET XACT_ABORT  ON;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRYBEGIN    TRAN;    -- Add Tutor    insert into    Tutor(         Name,  IdentityCard,  Age,  Gender, Photo)values    (@username, @cardid, @age, @sex, @image_path);-- 獲取自增IDselect    @userid = scope_identity();-- Add Certificateinsert into    Certificate(         TutorId,  JobId,  LevelId,  BeginTime,EndTime,Id, ImageUrl)values    (@userid, @job_base_id, @level_base_id,@BeginTime, @EndTime,@certificateid, @certificate_path);-- 獲取自增IDselect    @ceid = scope_identity();COMMIT    TRAN;END TRYBEGIN CATCHROLLBACK TRAN; throw;END CATCH
END/* 
USE [GmkCollege]
GODECLARE    @return_value int,@userid int,@ceid intEXEC    @return_value = [dbo].[CollegeAdd]@username = '測試8',@cardid = '測試8',@age = 99,@sex = '女',@image_path = '測試8',@job_base_id = 2,@level_base_id = 2,@certificateid = '測試8',@certificate_path = '測試8',@BeginTime='2016-06-06',@EndTime='2017-01-01',@userid = @userid OUTPUT,@ceid = @ceid OUTPUTSELECT    @userid as N'@userid',@ceid as N'@ceid'SELECT    'Return Value' = @return_valueGO 
*/

?

-- 鄒柯
Create PROCEDURE AddSignUpInfo
(--UserInfo@Name NVarChar(18)=null           ,@IdCard VarChar(18)=null     ,@Gender Char(2)=null                 ,@Area NVarChar(30)=null          ,@Nation NVarChar(15)=null         ,@Hobby NVarChar(50)=null         ,@BirthDate DateTime=null         ,@Phone VarChar(11)=null           ,@TrainStatus Char(2)=null      --UserTransInfo      ,@TransOrganizate NVarChar(30)=null,@Certicate NVarChar(30)=null     ,@StartTime DateTime=null        ,@EndTime DateTime=null          ,@Expectation NVarChar(300)=null ,@TransContent NVarChar(300)=null  --UserPayInfo,@OrderNo Varchar(30)=null    ,@Type Int=1,@Amount Int=null,@Currency Int=2,@Method Int=1,@Status Int=1
)
WITH ENCRYPTION
AS
BEGINSET NOCOUNT     ON;SET XACT_ABORT  ON;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRYBEGIN    TRAN;    --聲明用戶ID變量declare @UID int=null;declare @INFO1 int=null;declare @INFO2 int=null;declare @INFO3 int=null;--根據身份證號獲取用戶IDSELECT @UID=ID FROM UserCaptcha WHERE IdCard=@IdCard;--根據用戶ID查詢該用戶之前是否已添加過報名信息--只是未支付成功或其他原因導致這條信息無效SELECT @INFO1=Count(1) FROM UserInfo WHERE UID=@UID;SELECT @INFO2=Count(1) FROM UserTransInfo WHERE UID=@UID;SELECT @INFO3=Count(1) FROM UserPayInfo WHERE UID=@UID;if @INFO1 = 0 begin-- Add UserInfo    insert into    UserInfo([UId],[Name],[Gender],[Nation],[Area],[Hobby],[BirthDate],[Phone],[TrainStatus])values(@UID,@Name,@Gender,@Nation,@Area,@Hobby,@BirthDate,@Phone,@TrainStatus)end    elsebegin-- Update UserInfo    Update UserInfo SET[Name]=@Name,[Gender]=@Gender,[Nation]=@Nation,[Area]=@Area,[Hobby]=@Hobby,[BirthDate]=@BirthDate,[Phone]=@Phone,[TrainStatus]=@TrainStatusWhere UId=@UIDendif @INFO2 = 0begin-- Add UserTransInfoinsert into    UserTransInfo([UId],[TransOrganizate],[Certicate],[TransContent],[Expectation],[StartTime],[EndTime])values(@UID,@TransOrganizate,@Certicate,@TransContent,@Expectation,@StartTime,@EndTime);endelsebegin-- Update UserTransInfoUpdate UserTransInfo SET [TransOrganizate]=@TransOrganizate,[Certicate]=@Certicate,[TransContent]=@TransContent,[Expectation]=@Expectation,[StartTime]=@StartTime,[EndTime]=@EndTimeWhere UId=@UIDendif @INFO3 = 0begin-- Add UserPayInfoinsert into    UserPayInfo([UId],[OrderNo],[Type],[Amount],[Currency],[Method],[Status])values(@UID,@OrderNo,@Type,@Amount,@Currency,@Method,@Status);endelsebegin-- Update UserPayInfoUpdate UserPayInfo SET [OrderNo]=@OrderNo,[Type]=@Type,[Amount]=@Amount,[Currency]=@Currency,[Method]=@Method,[Status]=@StatusWhere UId=@UIDendCOMMIT    TRAN;END TRYBEGIN CATCHROLLBACK TRAN; throw;END CATCH
END/*
USE [GmkCollege]
GODECLARE    @return_value intEXEC    @return_value = [dbo].[AddSignUpInfo]@Name = 'zouke',@IdCard = '111111',@Gender = 1,@Area = 'zzzzzzzz',@Nation = '漢族',@Hobby = '足球、籃球呢',@BirthDate = '2016-01-01',@Phone = '18761939022',@TrainStatus = 1,@TransOrganizate = 'GMK',@Certicate = '理療師',@StartTime = '2011-01-01',@EndTime = '2012-01-01',@Expectation = 'cccccccc',@TransContent = 'ddddddd',@OrderNo="2222222222",@Type=1,@Amount=100,@Currency=2,@Method=1,@Status=1,SELECT    'Return Value' = @return_valueGO
*/

?