虽然SQL服务器中的循环只在条件变化 [英] While loop in SQL server varying only where conditions

问题描述

嗨gud晚上，



我有一个问题，我需要使用WHILE循环编写一个动态查询，只有更改是在where where condition。所有where条件应该在一个SP中执行



这是我的查询

Hi gud evening,

i have one problem, i need to write one dynamic query using WHILE loop, only change is in where condition. all the where condition should execute in one SP

this is my query

SET STATISTICS IO ON;

SELECT [Ticket Id],[Ticket Status],[Ticket Type],FromOperator,ToOperator,[Department],[CreateDate],[Oldest Ticket Date],[Oldest Ticket Number]
FROM (
SELECT  DISTINCT ETMIntId[Ticket Id],ETMVarTicketStatus[Ticket Status],
        ETMVarTicketType[Ticket Type],
        ETDVarLogin AS FromOperator,
        UsrVarName AS ToOperator,
        GenVarDesc[Department],
        CONVERT(VARCHAR(15),ETMDteDateTime,101) [CreateDate],
        (SELECT MIN(ETMDteDateTime) FROM dbo.EsTHMst WITH(NOLOCK) WHERE UsrIntId=ETMIntCurAssignTo)[Oldest Ticket Date],
        (SELECT MIN(ETMIntId) FROM dbo.EsTHMst WITH(NOLOCK) WHERE UsrIntId=ETMIntCurAssignTo)[Oldest Ticket Number]

FROM EsTHMst WITH(NOLOCK)
                            INNER JOIN EsTHDtl ON ETMIntId=ETDIntTHMId
                            LEFT JOIN CbzUsrMst ON UsrIntId=ETMIntCurAssignTo
                            LEFT JOIN dbo.cbzgenmst ON  UsrIntGrpGenId=GenIntId AND GenChrType=6 AND UsrChrOperatorStatus='Y'

WHERE ETMVarTicketStatus='Open'
AND ETMVarTicketType='Internal' AND UsrVarName<>'' AND  UsrChrOperatorStatus='Y'
) AS TBL1
WHERE TBL1.[Department]='Accounts'

在账户中，价值可能会改变....比如CET ...

please帮帮我



问候

dhana



SHOUTING删除 - OriginalGriff [/ edit]

in the place of accounts, value may be changed....like CET...
please help me

Regards
dhana

[edit]SHOUTING removed - OriginalGriff[/edit]

推荐答案

你需要创建存储过程 [ ^ ]并在循环中调用它

You need to create stored procedure[^] and call it in a loop

Create Procedure DoSomething
    @dept NVARCHAR(30) 
AS
BEGIN
   --your query here
   WHERE TBL1.[Department]=@dept
END

要在查询中循环部门，请阅读公用表表达式 [ ^ ]。您需要对CTE使用递归查询：使用公用表表达式的递归查询 [ ^ ]





要循环使用C＃代码，请参阅：

演练：仅使用存储过程（C＃） [ ^ ]

如何：使用ADO.NET和Visual C＃.NET调用参数化存储过程 [ ^ ]

To loop Departments in query, read about Common Table Expressions[^]. You need to use recursive query with CTE: Recursive Queries Using Common Table Expressions[^]


To loop through the C# code, see these:
Walkthrough: Using Only Stored Procedures (C#)[^]
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]

这篇关于虽然SQL服务器中的循环只在条件变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！