SqlServer存储过程用到一些总结 小白总结嘻嘻

本文主要是介绍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存储过程用到一些总结 小白总结嘻嘻的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---