几个较为实用的sql server 语句功能

//游标输出每一项。直接在sqlserver中执行。

     declare @Value varchar(100)

     declare mycursor cursor for select UserID from SysUser
     open mycursor
     
     fetch next from mycursor into @Value   
       while(@@fetch_status=0)   
      begin
         print @Value
         fetch next from mycursor into @Value  
      end
     close mycursor      --关闭游标
     deallocate mycursor

//执行存储过程

declare @SqlZuizhong varchar(50)
exec PROC_HR_SA_XXX 1,3,'00101','','00101,00105,00102','I',@SqlZuizhong output
print '输出的是：'+@SqlZuizhong

exec PROC_HR_SA_XXX 1,3,'00102','BA',' and d.EmpName like ''''%%''''

//存储过程
USE [IBR_App_XX]
GO
/****** Object:  StoredProcedure [dbo].[PROC_HR_SA_XXX] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[PROC_HR_SA_XXX]
(
 @CourseId varchar(10),        --期间
 @Schemeid varchar(10),        --薪资设计
 @SubjectId  varchar(20),      --科目
 @StrWhere varchar(100),       --条件查询
 @SubjectClom varchar(max),    --查询科目
 @CompanyCode varchar(100),    --公司编号
 @SqlZuizhong varchar(max) ='' output
)
AS
     declare @showColumn varchar(max)=''
     declare @colunName varchar(50)
     declare @Value varchar(2000)
     declare @sql varchar(max)
     declare @sqlstr varchar(2000)
     declare @courseDetail varchar(max)=''
     declare @subject varchar(1000)
     declare @num int=0;
     declare @lastWhere varchar(1000)=' and (';
     declare @sqlsstr varchar(1000)=''
     declare @strNum varchar(1000)='s'
     IF(@SubjectId IS NULL OR @SubjectId='')RETURN
     ------查询字表单的组成科目
     select @showColumn=isnull(t.EngColumn,'') from HR_SA_DE_Sss
     where SalarySchemeId=@Schemeid and t.SalaryItemId=@SubjectId
     -------字表单自己科目编号显示
     select @colunName=Id from HR_SA_DE_SalaryItem where Id=@SubjectId
     ----判断是否
     if @showColumn !=''
     begin
        set @showColumn=@ShowColumn+','+@colunName;
     end
     else
     begin
         set @showColumn=@colunName
     end
     ----列名称
     set @ShowColumn=REPLACE(@ShowColumn,']','');---替换
     set @ShowColumn=REPLACE(@ShowColumn,'[','');----替换
     set @subject=REPLACE(@ShowColumn,' ','');  ---替换
     set @subject = @SubjectClom
     
     print @subject
     --定义列的名称
     set @sql=' select d.Id,d.EmpName ,d.EmpCode,d.公司ID ,'+@subject+'  from (select emp.Id,emp.EmpId,emp.EmpName,emp.EmpCode,emp.EntryTime,emp.DimissionTime,emp.CourseId,emp.SchemeId,(select Name from HR_SA_Sad where Id = emp.CourseId) as "薪资期间",(select SalaryScheme from HR_SA_SalaryScheme where Id = emp.SchemeId) as "薪酬方案",(select CompanyCode from HR_SA_Eee where id = emp.EmpId) as "公司ID"  from HR_SA_DE_Sadasg emp) d '
     declare mycursor cursor for Select short_str FROM F_SQLSERVER_SPLIT(@subject,',')   --分割数据 为所获得的数据集指定游标
     open mycursor
     fetch next from mycursor into @Value -----遍历字表单科目
       while(@@fetch_status=0)   
     begin
         set @strNum=@strNum+LTRIM(RTRIM(STR(@num)))---自动生成表别名
         set @sqlstr= ' '+@strNum+'.Salary as '''+@Value+'''' ----字段数据
        
         set @sql=REPLACE(@sql,@Value,@sqlstr)----替换掉之前定义的列
         set @courseDetail=@courseDetail+' left join  HR_SA_DE_EmpCourseDetail '+@strNum+' on ('+@strNum+'.SubjectItemId='''+@Value+''' and '+@strNum+'.empId=d.EmpId and '+@strNum+'.CourseId =d.CourseId and '+@strNum+'.SchemeId=d.SchemeId )'
         set @num=@num+1;
         set @sqlsstr=@sqlsstr+' or  Convert(decimal,'+@strNum+'.Salary)!=0 '
         print str(@num)
         fetch next from mycursor into @Value  
      end
     close mycursor      --关闭游标
     deallocate mycursor
     set @sqlsstr=substring(@sqlsstr,4,len(@sqlsstr));
    
     set @lastWhere=@lastWhere+''+@sqlsstr+')'
    
     set @sql=@sql+@courseDetail+ ' where  d.CourseId=Convert(int,'''+@CourseId+''')   and d.SchemeId=Convert(int,'''+@Schemeid+''') '+@StrWhere
     print @sql
     set @SqlZuizhong = '9999'
     if @CompanyCode = ''
     begin
        set @sql=' select * from ( ' + @sql + ' ) t where  t.公司ID is null order by t.EmpName  '
     end
     else
     begin
        set @sql=' select * from ( ' + @sql + ' ) t where  t.公司ID = '''+@CompanyCode+''' order by t.EmpName  '
     end
     set @SqlZuizhong = @SubjectClom
     print @SqlZuizhong
     exec (@sql)
    
    //函数
USE [IBR_App_XX]
GO
/****** Object:******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[F_SQLSERVER_SPLIT](@Long_str NVARCHAR(MAX),@split_str NVARCHAR(100))    
 RETURNS  @tmp TABLE(        
     ID          inT     IDENTITY PRIMARY KEY,      
     short_str   NVARCHAR(MAX)    
 )    
 AS   
 BEGIN   
     DECLARE @short_str NVARCHAR(MAX),@split_str_length int,@split_str_Position_Begin int
     SET @split_str_length = LEN(@split_str)
     SET @Long_str=REPLACE(REPLACE(@Long_str,CHAR(10),''),CHAR(13),'')
     IF CHARINDEX(@split_str,@Long_str)=1
          SET @Long_str=STUFF(@Long_str,1,@split_str_length,'')
     IF CHARINDEX(@split_str,@Long_str)=0
         INSERT INTO @tmp SELECT @Long_str
     ELSE
         BEGIN
             WHILE 1>0    
                 BEGIN   
                     SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)
                     SET @short_str=LEFT(@Long_str,@split_str_Position_Begin-1)
                     IF @short_str<>'' INSERT INTO @tmp SELECT @short_str  
                     SET @Long_str=STUFF(@Long_str,1,@split_str_Position_Begin+@split_str_length-1,'')
                     SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)
                     IF @split_str_Position_Begin=0
                     BEGIN
                         IF LTRIM(@Long_str)<>''
                             INSERT INTO @tmp SELECT @Long_str
                         BREAK
                     END
                END           
         END
     RETURN     
 END

//触发器

USE [IBR_App_HR]
GO

/****** Object:  Trigger [dbo].[Trigger_SA_Employee]    Script Date: 12/29/2014 18:50:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[Trigger_SA_Employee]
on [dbo].[HR_HM_XXX]
for insert,update,delete
as
    --更新
if exists
(select A.Id,A.Name,A.Code,A.EntryTime,A.DimissionTime from inserted A,deleted b where A.Id = B.Id)
    begin
        if exists (select dbo.HR_SA_XXX.Id from dbo.HR_SA_XXX,inserted where dbo.HR_SA_XXX.Id = inserted.Id)
        
         update dbo.HR_SA_XXX set Name = Emp.Name,Code=emp.Code,EntryTime = emp.EntryTime,DimissionTime = emp.DimissionTime,Remark =emp.Remark
          
          from dbo.HR_HM_XXX Emp,dbo.HR_SA_XXX SaEmp where Emp.Id = SaEmp.Id
     end
else
    begin
    --插入
         if exists (select Id from inserted)
         begin
            insert into dbo.HR_SA_XXX (Id,Name,Code,EntryTime,DimissionTime,Remark)  
            (select Id, Name,Code,EntryTime,DimissionTime,'Insert' from inserted);
         end
    --删除
         else
            begin
             update dbo.HR_SA_XXX set Remark = 'Delete' from  dbo.HR_SA_XXX,deleted where  dbo.HR_SA_XXX.id = deleted.id;
            end
    end

GO

//视图
USE [IBR_App_XX]
GO

/****** Object:  View [dbo].[V_HR_SA_Schemeooo]    Script Date: 11/29/2014 18:27:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create VIEW [dbo].[V_HR_SA_Schemeooo]
AS
SELECT    HR_SA_DE_XXXooo.oooType,HR_SA_DE_XXXooo.Name,HR_SA_DE_SchemeSubject.Calformal, HR_SA_DE_SchemeSubject.CalformalEng,
                      HR_SA_DE_SchemeSubject.SubtracSequence,HR_SA_DE_SchemeSubject.XXXoooId, HR_SA_DE_SchemeSubject.Id,
                      HR_SA_DE_SchemeSubject.XXXSchemeId
FROM         HR_SA_DE_XXXooo INNER JOIN
                      HR_SA_DE_SchemeSubject ON HR_SA_DE_XXXooo.Id = HR_SA_DE_SchemeSubject.XXXoooId
GO