SQL条件选择 [英] SQL conditional SELECT

问题描述

我想创建一个存储过程，该存储过程的参数指示应该选择哪些字段.

I would like to create a stored procedure with parameters that indicate which fields should be selected.

例如我想将两个参数"selectField1"和"selectField2"分别作为布尔值传递.

E.g. I would like to pass two parameters "selectField1" and "selectField2" each as bools.

然后我想要类似的东西

SELECT

if (selectField1 = true) Field1 ELSE do not select Field1

if (selectField2 = true) Field2 ELSE  do not select Field2

FROM Table

谢谢 卡尔

推荐答案

听起来像他们想要只返回允许的字段的声音，这意味着返回的字段数也必须是动态的.这将使用2个变量.除此之外，任何其他事情都会变得令人困惑.

Sounds like they want the ability to return only allowed fields, which means the number of fields returned also has to be dynamic. This will work with 2 variables. Anything more than that will be getting confusing.

IF (selectField1 = true AND selectField2 = true)
BEGIN
   SELECT Field1, Field2
   FROM Table
END
ELSE IF (selectField1 = true)
BEGIN
   SELECT Field1
   FROM Table
END
ELSE IF (selectField2 = true)
BEGIN
   SELECT Field2
   FROM Table
END

动态SQL将帮助倍数.此示例假设至少有1列为真.

Dynamic SQL will help with multiples. This examples is assuming atleast 1 column is true.

DECLARE @sql varchar(MAX)
SET @sql = 'SELECT '
IF (selectField1 = true)
BEGIN
   SET @sql = @sql + 'Field1, '
END
IF (selectField2 = true)
BEGIN
   SET @sql = @sql + 'Field2, '
END
...
-- DROP ', '
@sql = SUBSTRING(@sql, 1, LEN(@sql)-2)

SET @sql = @sql + ' FROM Table'

EXEC(@sql)

这篇关于SQL条件选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！