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 DepartmentInfo
                 where  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 OUTPUT
   set @idx =1;
   set @num =1;
   set @Delimiter = ',';
   set @dep_henkomae =null
   set @fac_henkomae =null;
   set @section_henkomae =null;
   set @subsection_henkomae =null;
   set @class_henkomae =null;
   set @shift_henkomae =null;
   while @idx != -1
   begin 
    SET @idx = CHARINDEX(@Delimiter,@DepartmentName);
    IF @idx != 0
       begin 
         SET @slice = LEFT(@DepartmentName,@idx - 1)
       end
    ELSE
       begin 
           SET @slice = @DepartmentName
        set @idx= -1;
       end
    SET @DepartmentName = RIGHT (@DepartmentName, LEN(@DepartmentName) - @idx)、
     if @num = 1 
     set @dep_henkomae = @slice;
    if @num = 2
     set @fac_henkomae = @slice;
    if @num = 3
     set @section_henkomae =@slice;
    if @num = 4
     set @subsection_henkomae = @slice;
    if @num = 5
     set @class_henkomae = @slice;
    if @num = 6
     set @shift_henkomae = @slice;
  set @num = @num+1
  end  

日期转为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])