本文主要是介绍SqlServer存储过程用到一些总结 小白总结嘻嘻，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
声明：
declare @i
赋值
set i = 0
将多行的字段ID用逗号分开
select @ApplyIDs=stuff((select ','+ CONVERT(nvarchar(50),[ID]) from ApplyMain t where BillState<>1 and NextApproverNo = @BeforeUserNo for xml path('')), 1, 1, '');//'111,222,333'
执行动态SQl
set @Sql = 'update ApplyMain set NextApproverNo = '''+@AfterUserNo +''' where ID in ('+ @ApplyIDs+')'
EXEC sp_executesql @Sql
执行动态SQL求Count
set @SqlDY = 'with cr as (select HigherDepID,ID from DepartmentInfo where ID =(select ID from DepartmentInfowhere DepartmentName = '''+@DepartmentName+''' )union all select d.HigherDepID,d.ID from cr c inner join DepartmentInfo d on c.ID = d.HigherDepID) select @DYCount=count(*) from EmployeeInfo where DepartmentID in (select ID from cr ) and delflag = 0'EXEC sp_executesql @SqlDY,N'@DYCount int output',@DYCount OUTPUT
将逗号分隔的字符串拆开 eg：‘制造,工厂,科,系,班,A’
set @Sql = 'with cr as (select HigherDepID,ID from DepartmentInfo_mb where ID= '+convert(varchar,@DepartmentID)+' union all select d.HigherDepID,d.ID from cr c inner join DepartmentInfo_mb d on c.HigherDepID = d.ID) select @DepartmentName=stuff((select '',''+ DepartmentName from DepartmentInfo_mb t where ID in (select ID from cr) and delFlag=0 order by ID for xml path('''')), 1, 1, '''')';EXEC sp_executesql @Sql,N'@DepartmentName nvarchar(500) output',@DepartmentName OUTPUTset @idx =1;set @num =1;set @Delimiter = ',';set @dep_henkomae =nullset @fac_henkomae =null;set @section_henkomae =null;set @subsection_henkomae =null;set @class_henkomae =null;set @shift_henkomae =null;while @idx != -1begin SET @idx = CHARINDEX(@Delimiter,@DepartmentName);IF @idx != 0begin SET @slice = LEFT(@DepartmentName,@idx - 1)endELSEbegin SET @slice = @DepartmentNameset @idx= -1;endSET @DepartmentName = RIGHT (@DepartmentName, LEN(@DepartmentName) - @idx)、if @num = 1 set @dep_henkomae = @slice;if @num = 2set @fac_henkomae = @slice;if @num = 3set @section_henkomae =@slice;if @num = 4set @subsection_henkomae = @slice;if @num = 5set @class_henkomae = @slice;if @num = 6set @shift_henkomae = @slice;set @num = @num+1end
日期转为YYYY-MM-DD日期格式
convert(date, [CreateTime],111)
日期转为YYYY_MM_DD字符串格式、YYYY/MM/DD
convert(varchar(20), [CreateTime],23)、convert(varchar(20), [CreateTime],111)
数字转为字符串格式
convert(varchar(20), [Id])
这篇关于SqlServer存储过程用到一些总结 小白总结嘻嘻的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
