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 null
begin
declare @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 OUTPUT
if @p > 0
begin
raiserror(@msg ,16,1)
return
end
else
begin
set @sqlString = 'insert into '+@strTable+' values ('+@strValues+')';
exec(@sqlString);
end
end
ELSE
begin
set @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
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRAN;
-- Add Tutor
insert into Tutor
( Name, IdentityCard, Age, Gender, Photo)
values (@username, @cardid, @age, @sex, @image_path);
-- 获取自增ID
select @userid = scope_identity();
-- Add Certificate
insert into Certificate
( TutorId, JobId, LevelId, BeginTime,EndTime,Id, ImageUrl)
values (@userid, @job_base_id, @level_base_id,@BeginTime, @EndTime,@certificateid, @certificate_path);
-- 获取自增ID
select @ceid = scope_identity();
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN; throw;
END CATCH
END
/*
USE [GmkCollege]
GO
DECLARE @return_value int,
@userid int,
@ceid int
EXEC @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 OUTPUT
SELECT @userid as N'@userid',
@ceid as N'@ceid'
SELECT 'Return Value' = @return_value
GO
*/
-- 邹柯
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
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRAN;
--声明用户ID变量
declare @UID int=null;
declare @INFO1 int=null;
declare @INFO2 int=null;
declare @INFO3 int=null;
--根据身份证号获取用户ID
SELECT @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
else
begin
-- Update UserInfo
Update UserInfo SET
[Name]=@Name
,[Gender]=@Gender
,[Nation]=@Nation
,[Area]=@Area
,[Hobby]=@Hobby
,[BirthDate]=@BirthDate
,[Phone]=@Phone
,[TrainStatus]=@TrainStatus
Where UId=@UID
end
if @INFO2 = 0
begin
-- Add UserTransInfo
insert into UserTransInfo(
[UId]
,[TransOrganizate]
,[Certicate]
,[TransContent]
,[Expectation]
,[StartTime]
,[EndTime]
)
values(
@UID
,@TransOrganizate
,@Certicate
,@TransContent
,@Expectation
,@StartTime
,@EndTime
);
end
else
begin
-- Update UserTransInfo
Update UserTransInfo SET
[TransOrganizate]=@TransOrganizate
,[Certicate]=@Certicate
,[TransContent]=@TransContent
,[Expectation]=@Expectation
,[StartTime]=@StartTime
,[EndTime]=@EndTime
Where UId=@UID
end
if @INFO3 = 0
begin
-- Add UserPayInfo
insert into UserPayInfo(
[UId]
,[OrderNo]
,[Type]
,[Amount]
,[Currency]
,[Method]
,[Status]
)
values(
@UID
,@OrderNo
,@Type
,@Amount
,@Currency
,@Method
,@Status
);
end
else
begin
-- Update UserPayInfo
Update UserPayInfo SET
[OrderNo]=@OrderNo
,[Type]=@Type
,[Amount]=@Amount
,[Currency]=@Currency
,[Method]=@Method
,[Status]=@Status
Where UId=@UID
end
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN; throw;
END CATCH
END
/*
USE [GmkCollege]
GO
DECLARE @return_value int
EXEC @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_value
GO
*/
