存储过程实例

 

例1 传入一个参数@username,判断用户是否存在

-------------------------------------------------------------------------------
CREATE PROC IsExistUser
(
@username varchar(20),
@IsExistTheUser varchar(25) OUTPUT--输出参数
)
as
SELECT @IsExistTheUser = count(username)
FROM users
WHERE username = @username
GO

例2 存储过程结合事务的一个例子：添加用户，涉及到两个表，一个用户资料表，一个密码表：

增加用户
-------------------------------------------------------------------------------
create procedure AddUser
(
@username varchar(20),--用户名
@passwords varchar(18),--密码
@email varchar(100),--邮箱
@qq varchar(11),--QQ号码
@imageid int--头像
)
as
-- 设置NOCOUNT为ON，使不再返回统计信息（存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。）
SET NOCOUNT ON

DECLARE @CurrentError int

-- 创建事务, 插入数据到2个表
BEGIN TRANSACTION

-- 创建用户信息
INSERT INTO users (username, email, qq, imageid)
VALUES (@username, @email, @qq, @imageid)

select @CurrentError = @@Error

IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END

-- 创建用户密码
INSERT INTO Signon (username, [password])
VALUES (@username, @passwords)

select @CurrentError = @@Error

IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END

-- 结束事务
COMMIT TRANSACTION

-- 恢复NOCOUNT设置为OFF
SET NOCOUNT OFF

-- 返回0表示成功，其他则为失败
RETURN 0

-- 错误处理
ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN @CurrentError
GO

 

 

3，SQL 获取当前时间

    --获得当月天数  
    select day(dateadd(mm,1,getdate())-day(getdate())) as 本月天数 ;  
    select getdate() as 当前日期;  
    select day(getdate()) as 目前第几天;  
    select getdate()-day(getdate())  as 上个月最后一天; -- 减去了当前的天数  
    select dateadd(mm,1,getdate())-day(getdate()) as 加上一个月; -- 也就是这个月的最后一天  
    select day(dateadd(mm,1,getdate())-day(getdate())) as 获得当月天数;  

 

4，转义字符：

WHERE ColumnA LIKE '%5/%%' ESCAPE '/'

5，使用实例

if (@@Error>0)
    return -4
else
    return @@Identity

 

Code
set xact_abort on
begin tran t1
     insert into Category(CategoryName) values(@CategoryName)
commit tran t1

 

Code
ALTER PROCEDURE [dbo].[prCategory]
(
    @CategoryID int=null,                 -- 类别ID
    @CategoryName varchar(100)=null,       -- 类别名称
    @QuestionnaireIDs varchar(100)=null,   -- 类别下的调查表ID
    @Action int     -- 1表示插入，@CategoryName 非空
                    -- 2表示更新，@CategoryID、@CategoryName 非空
                    -- 3表示删除，@CategoryID 非空
                    -- 4表示查询，@CategoryID 非空
                    -- 5表示查询所有记录
)
AS
BEGIN
    declare @currentID int

    -- 检查参数@Action
    if (@Action is null or @Action<1 or @Action>5)    
        return   
    
    -- 插入记录
    if (@Action=1)      
    begin
        -- 检查参数@CategoryName是否为空
        if (@CategoryName is null or len(@CategoryName)=0)
            goto ParameterError

        -- 检查记录是否存在        
        select @currentID=CategoryID from Category where CategoryName=@CategoryName
        if (@CurrentID>0)
            goto RowRepeatError
        
        -- 插入记录
        set xact_abort on
        begin tran t1
        insert into Category(CategoryName) values(@CategoryName)
        commit tran t1

        -- 返回插入行的标识列的列值
        if (@@Error>0)
            return -4
        else
            return @@Identity
    end

    -- 更新CategoryName字段值
    else if (@Action=2)  
    begin
        -- 检查参数@CategoryID是否有效
        if (@CategoryID is null or len(@CategoryID)=0)            
            goto ParameterError

        -- 检查参数@CategoryName是否为空
        if (@CategoryName is null or len(@CategoryName)=0)
            goto ParameterError

        -- 检查记录是否存在
        select @CurrentID=CategoryID from Category where CategoryID<>@CategoryID and CategoryName=@CategoryName
        if (@CurrentID>0)
            goto RowRepeatError
        
        -- 更新记录
        set xact_abort on
        begin tran t2
        update Category set CategoryName=@CategoryName where CategoryID=@CategoryID
        commit tran t2

        goto ReturnResult
    end    

    -- 删除记录
    else if (@Action=3)              
    begin
        -- 检查参数@CategoryID是否有效
        if (@CategoryID is null or @CategoryID<1)
            goto ParameterError

        -- 删除记录
        set xact_abort on
        begin tran t3

        -- 删除子问题答案
        delete from TextAnswer where QuestionID in (
            select b.QuestionID from Question as a join Question as b on a.QuestionID=b.ParentQuestionID
            where b.QuestionType=4 and a.QuestionnaireID in (
                select QuestionnaireID from Questionnaire where CategoryID=@CategoryID
            )            
        )
        
        -- 删除问题答案
        delete from TextAnswer where QuestionID in (
            select QuestionID from Question where QuestionnaireID in (
                select QuestionnaireID from Questionnaire where CategoryID=@CategoryID and QuestionType=4
            )
        )

        -- 删除子问题答案
        delete from ChoiceAnswer where QuestionID in (
            select b.QuestionID from Question as a join Question as b on a.QuestionID=b.ParentQuestionID
            where b.QuestionType<>4 and a.QuestionnaireID in (
                select QuestionnaireID from Questionnaire where CategoryID=@CategoryID
            )
        )

        -- 删除问题答案
        delete from ChoiceAnswer where QuestionID in (
            select QuestionID from Question where QuestionnaireID in (
                select QuestionnaireID from Questionnaire where CategoryID=@CategoryID
            )
        )

        -- 删除子问题
        delete from Question where ParentQuestionID in (
            select QuestionID from Question where QuestionnaireID in (
                select QuestionnaireID from Questionnaire where CategoryID=@CategoryID and QuestionType<>4
            )
        )

        -- 删除问题
        delete from Question where QuestionnaireID in (
            select QuestionnaireID from Questionnaire where CategoryID=@CategoryID
        )
        
        -- 删除调查问卷
        delete from Questionnaire where CategoryID=@CategoryID

        -- 删除组
        delete from Category where CategoryID=@CategoryID
        commit tran t3

        goto ReturnResult
    end 

    -- 根据@CategoryID查询记录 
    else if (@Action=4)
    begin
        -- 检查参数@CategoryID是否合法
        if (@CategoryID is null or  @CategoryID<1)
            goto ParameterError

        -- 查询记录
        select * from Category where CategoryID=@CategoryID

        goto ReturnResult
    end 

    -- 查询所有记录
    else if (@Action=5)
    begin
        -- 查询记录
        select * from Category

        goto ReturnResult
    end

    RowRepeatError:
        return -2

    ParameterError:
        return -3

    ReturnResult:
        if (@@Error>0)
            return -4
        else
            return 0
END