在SQL Server存储过程SELECT语句中嵌套if语句 [英] Nested if statements in SQL Server stored procedure SELECT statement

问题描述

我是新来的，而且对存储过程来说还比较新，所以请多多包涵！我已经在此处检查了相关问题，在此实例中找不到任何有效的方法.

I'm new here, and relatively new to stored procedures, so please bear with me! I've checked related questions on here and can't find anything that works in this instance.

我正在尝试构建一个存储过程(MS SQL Server 2005)，该过程采用许多传入的值，并且实际上就像在嵌入式SQL中一样动态地构建SQL.

I am trying to build a stored procedure (MS SQL Server 2005) that takes a number of passed in values and in effect dynamically builds up the SQL as you would with inline SQL.

这是我一直未解脱的地方.

This is where I've come unstuck.

我们有(为简化起见，已进行了一些简化):

We have (somewhat simplified for clarity):

@searchf1 varchar(100),   -- search filter 1
@searchr1 varchar(100),   -- search result 1
@searchf2 varchar(100),   -- search filter 2
@searchr2 varchar(100),   -- search result 2
@direction char(1),   -- direction to order results in
AS

set nocount on
set dateformat dmy

SELECT *
  FROM database.dbo.table T
 WHERE T.deleted = 'n'
ORDER BY CASE @direction
            WHEN 'A' THEN T.id
            WHEN 'D' THEN T.id DESC
         END

END

set nocount off

我也尝试过ORDER BY的代码行:

I have also tried the lines from ORDER BY as:

IF @direction = 'N' THEN
     ORDER BY 
          T.id
ELSE
     ORDER BY
          T.id DESC

两种方法都给我一个错误:

Both approaches give me an error along the lines:

关键字'DESC'附近的语法不正确." (它引用了最后一个ORDER BY之后的行ID DESC

"Incorrect syntax near the keyword 'DESC'." (which references the line id DESC following the final ORDER BY

作为此存储过程的一部分，我还想尝试输入匹配的值对，这些值对引用要查找的字段和要与之匹配的字段，这些值可以存在也可以是''.为此，我需要在SELECT部分​​中添加类似于以下内容的代码:

As part of this stored procedure I also want to try to feed in matched pairs of values which reference a field to look up and a field to match it to, these could either be present or ''. To do that I need to add into the SELECT section code similar to:

WHERE 
     deleted = 'n'
     IF @searchf1 <> '' THEN
         AND fieldf1 = @searchf1 AND fieldr1 = @searchr1

但是这会产生如下错误:

This however generates errors like:

关键字"IF"附近的语法不正确.

Incorrect syntax near the keyword 'IF'.

我知道这种动态SQL并不是最优雅的.而且我知道我可以使用glocal IF ELSE语句来完成此操作，但是如果执行SP，则将有数千行.最多有15对这些搜索字段，以及指示该方向的方向和字段.

I know dynamic SQL of this type isn't the most elegant. And I know that I could do it with glocal IF ELSE statements, but if I did the SP would be thousands of lines long; there are going to up to 15 pairs of these search fields, together with the direction and field to order that direction on.

(此SP的当前版本使用传入的ID列表来返回由某些内联动态SQL生成的ID，通过这样做，我试图将其减少为一击以生成记录集)

(the current version of this SP uses a passed in list of IDs to return generated by some inline dynamic SQL, through doing this I'm trying to reduce it to one hit to generate the recordset)

任何帮助，我们将不胜感激.为了清楚起见，我已经大大简化了上面示例中的代码，因为它是我要查询的带有SELECT和ORDER BY的嵌套IF语句的一般概念.

Any help greatly appreciated. I've hugely simplified the code in the above example for clarity, since it's the general concept of a nested IF statement with SELECT and ORDER BY that I'm inquiring about.

推荐答案

尝试一下:

SELECT *  FROM database.dbo.table T WHERE T.deleted = 'n'
ORDER BY 
CASE WHEN @direction='A' THEN T.id END ASC,
CASE WHEN @direction='D' THEN T.id END DESC         

来源文章:

Source Article:
http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/

这篇关于在SQL Server存储过程SELECT语句中嵌套if语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！