sql存储过程实例
1、 goto 的使用:
2、事务及set xact_abort on的使用:
3、@Error的使用:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
if (@@Error>0)
return -4
else
return @@Identity
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
set xact_abort on
begin tran t1
insert into Category(CategoryName) values(@CategoryName)
commit tran t1
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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
4、 使用sql语句获得当前月天数
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
--获得当月天数
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 获得当月天数;
5、转义符
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'