如何将语句放入变量中并在存储过程中多次使用 [英] How to put a statement in a variable and use it multiple times in stored procedure

问题描述

我创建了一个存储过程，其中一个 case 语句将被使用 5 次，那么有什么方法可以创建一个 SQL 变量，以便我可以多次使用该变量?我创建了一个存储过程，其中一个 case 语句将被使用 5 次，那么有没有办法创建一个 SQL 变量，以便我可以多次使用该变量?

I have created a stored procedure where one case statement will be used 5 times, so is there any way to create a SQL variable so I can use that variable multiple times? I have created a stored procedure where one case statement will be used 5 times, so is there any way to create a SQL variable so I can use that variable multiple times?

@Designation_Level1 varchar(100) = null ,@pk_Designation_ID varchar(100) = null

@Designation_Level1 varchar(100) = null , @pk_Designation_ID varchar(100) = null

AS
开始
声明@JoiningDateChkforRule date=null声明@MPHILDateChkforRule date=null声明@caseVar varchar(max)
设置@caseVar=''SET @JoiningDateChkforRule = '1991-09-19 00:0:00.000'SET @MPHILDateChkforRule = '1993-12-31 00:0:00.000'

AS
BEGIN
declare @JoiningDateChkforRule date=null declare @MPHILDateChkforRule date=null declare @caseVar varchar(max)
set @caseVar='' SET @JoiningDateChkforRule = '1991-09-19 00:0:00.000' SET @MPHILDateChkforRule = '1993-12-31 00:0:00.000'

set @Sql='SELECT Distinct SR.pk_employee_ID ,ISNULL(Last_Name,'''')+ '' ''+ISNULL(First_Name,'''')+'' ''+ISNULL(Middle_Name,'''') AS EmpName,Emp_Code,
TJoiningDateforRuleDesg.JoiningDateforRuleDesg,
-- if main designation and rule designation are same
--Main designation
CASE '+@pk_Designation_ID+' 
WHEN ' +cast(@pk_Designation_IDForRule as varchar) +' THEN 
--******************    
CASE    WHEN DATEDIFF(DAY, TJoiningDateforRuleDesg.JoiningDateforRuleDesg, '+cast(@JoiningDateChkforRule as varchar)+') > 0
    THEN TJoiningDateforRuleDesg.JoiningDateforRuleDesg
    ELSE 
        CASE    WHEN DATEDIFF(day, isnull(NETSETQual.NETSETPassing_Date,MPHILQual.MPHILQualPassing_Date), isnull(MPHILQual.MPHILQualPassing_Date,NETSETQual.NETSETPassing_Date)) >= 0
                THEN 
                    CASE    WHEN DATEDIFF(day, NETSETQual.NETSETPassing_Date, TJoiningDateforRuleDesg.JoiningDateforRuleDesg) <= 0
                            THEN NETSETQual.NETSETPassing_Date
                            ELSE 
                                CASE    WHEN NETSETQual.NETSETPassing_Date IS NULL
                                        THEN 
                                            CASE    WHEN DATEDIFF(DAY, MPHILQual.MPHILQualPassing_Date,'+CAST(@MPHILDateChkforRule as varchar) +')<= 0
                                                    THEN  MPHILQual.MPHILQualPassing_Date 
                                                    ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                                            END
                                        ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                                END
                    END
                ELSE 
                    CASE    WHEN DATEDIFF(day, MPHILQual.MPHILQualPassing_Date, TJoiningDateforRuleDesg.JoiningDateforRuleDesg) <= 0
                            THEN 
                                CASE    WHEN DATEDIFF(DAY, MPHILQual.MPHILQualPassing_Date,'+CAST(@MPHILDateChkforRule as varchar) +') <= 0
                                        THEN  MPHILQual.MPHILQualPassing_Date 
                                        ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                                END
                            ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                    END                     
        END
END         
--******************                            
ELSE -- if main designation and rule designation are different
(Select min(Joining_Date) from HRMS_Employee_ServiceRecord  (nolock) 
where fk_Designation_ID in (SELECT * from dbo.Split('''+@pk_Designation_ID+''','','')) 
and  pk_Employee_ID = SR.pk_Employee_ID) 
END as Senority_Joining_For_Order,  
-- Level_1_Designation
CASE '+@Designation_Level1+'
WHEN '+cast(@pk_Designation_IDForRule as varchar) +' THEN 
--******************    
CASE    WHEN DATEDIFF(DAY, TJoiningDateforRuleDesg.JoiningDateforRuleDesg, '+cast(@JoiningDateChkforRule as varchar)+') > 0
    THEN TJoiningDateforRuleDesg.JoiningDateforRuleDesg
    ELSE 
        CASE    WHEN DATEDIFF(day, isnull(NETSETQual.NETSETPassing_Date,MPHILQual.MPHILQualPassing_Date), isnull(MPHILQual.MPHILQualPassing_Date,NETSETQual.NETSETPassing_Date)) >= 0
                THEN 
                    CASE    WHEN DATEDIFF(day, NETSETQual.NETSETPassing_Date, TJoiningDateforRuleDesg.JoiningDateforRuleDesg) <= 0
                            THEN NETSETQual.NETSETPassing_Date
                            ELSE 
                                CASE    WHEN NETSETQual.NETSETPassing_Date IS NULL
                                        THEN 
                                            CASE    WHEN DATEDIFF(DAY, MPHILQual.MPHILQualPassing_Date,'+CAST(@MPHILDateChkforRule as varchar) +')<= 0
                                                    THEN  MPHILQual.MPHILQualPassing_Date 
                                                    ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                                            END
                                        ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                                END
                    END
                ELSE 
                    CASE    WHEN DATEDIFF(day, MPHILQual.MPHILQualPassing_Date, TJoiningDateforRuleDesg.JoiningDateforRuleDesg) <= 0
                            THEN 
                                CASE    WHEN DATEDIFF(DAY, MPHILQual.MPHILQualPassing_Date,'+CAST(@MPHILDateChkforRule as varchar) +') <= 0
                                        THEN  MPHILQual.MPHILQualPassing_Date 
                                        ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                                END
                            ELSE TJoiningDateforRuleDesg.JoiningDateforRuleDesg 
                    END                     
        END
END     

--******************                                
ELSE
(Select min(Joining_Date) from HRMS_Employee_ServiceRecord  (nolock) 
where fk_Designation_ID in (SELECT * from dbo.Split('''+@Designation_Level1+''','','')) 
and  pk_Employee_ID = SR.pk_Employee_ID) 
END as Level_1_Designation
FROM HRMS_EMPLOYEE_SERVICERECORD SR(NOLOCK)
INNER JOIN HRMS_Mst_Employee MstEmp (NOLOCK) ON SR.pk_Employee_ID=MstEmp.pk_Employee_ID AND Sr.IsCurrent_Appointment=''1'' AND Sr.fk_SeparationType_ID is null   AND Sr.Is_Approved=''1''   

INNER JOIN 
(
Select pk_Employee_ID, min(Joining_Date) as JoiningDateforRuleDesg  from HRMS_Employee_ServiceRecord  (nolock) 
where fk_Designation_ID = '+CAST(@pk_Designation_IDForRule as varchar)+'
group by pk_Employee_ID
)TJoiningDateforRuleDesg ON TJoiningDateforRuleDesg.pk_Employee_ID = SR.pk_Employee_ID

LEFT JOIN 
(
Select Distinct RuleEQ.pk_Employee_ID as NETSETpk_Employee_ID, min(RuleEQ.Passing_Date) as NETSETPassing_Date
FROM HRMS_Employee_Qualifications RuleEQ        
WHERE
fk_Course_ID in  (SELECT * from dbo.Split('''+(@NETSETQualification)+''','','')) 
group by RuleEQ.pk_Employee_ID
)NETSETQual ON
NETSETQual.NETSETpk_Employee_ID = SR.pk_Employee_ID

LEFT JOIN 
(
Select Distinct RuleEQ.pk_Employee_ID as MPHILQualpk_Employee_ID, min(RuleEQ.Passing_Date) as MPHILQualPassing_Date
FROM HRMS_Employee_Qualifications RuleEQ        
WHERE 
fk_Course_ID in   (SELECT * from dbo.Split('''+(@MPHILQualification)+''','','')) 
group by RuleEQ.pk_Employee_ID
)MPHILQual ON 
MPHILQual.MPHILQualpk_Employee_ID = SR.pk_Employee_ID   
LEFT JOIN HRMS_Employee_Qualifications RuleEQ ON RuleEQ.pk_Employee_ID = SR.pk_employee_ID
where 
MstEmp.deleted=''0'' and 
SR.Appointment_Type in (''3'' , ''2'') 
and SR.fk_SeparationType_ID is null and
SR.fk_Designation_ID='+ cast(@pk_Designation_IDForRule as varchar)+' AND        
( (TJoiningDateforRuleDesg.JoiningDateforRuleDesg < '+cast(@JoiningDateChkforRule as varchar)+')
        OR
(TJoiningDateforRuleDesg.JoiningDateforRuleDesg >= '+cast(@JoiningDateChkforRule as varchar)+' AND RuleEQ.fk_Course_ID IN (SELECT * from dbo.Split('''+@NETSETQualification+''','',''))) 
        OR
(TJoiningDateforRuleDesg.JoiningDateforRuleDesg >= '+cast(@JoiningDateChkforRule as varchar)+' AND RuleEQ.fk_Course_ID IN (SELECT * from dbo.Split('''+@MPHILQualification+''','','')) )    ) '
-- and ES.pk_CourseBranch_ID in (35) 

end

推荐答案

不，你不能把它放到一个变量中，除非你会一直使用动态 SQL.

No, you can't put it into a variable, unless you'll be using dynamic SQL all over.

根据代码的实际外观，您可以使用视图(或函数)或 CTE 来封装它的一部分.

Depending on what the code actually look like, you can use a view (or a function) or a CTE to encapsulate the part of it.

我宁愿继续写五遍，并期待更好的表现.

I'd rather stay with writing it five times, and expect the better performace.

这篇关于如何将语句放入变量中并在存储过程中多次使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！