CODEWITHSUNDEEP
sqlselectconditional-statements
Karl
Karl

Reputation: 1317

SQL conditional SELECT

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

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

Then I want something like

SELECT

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

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

FROM Table

Thanks Karl

Upvotes: 45

Views: 188351

Answers (7)

Domingos
Domingos

Reputation: 31

The noob way to do this:

SELECT field1, field2 FROM table WHERE field1 = TRUE OR field2 = TRUE

You can manage this information properly at the programming language only doing an if-else.

Example in ASP/JavaScript

// Code to retrieve the ADODB.Recordset
if (rs("field1")) {
   do_the_stuff_a();
}
if (rs("field2")) {
   do_the_stuff_b();
}
rs.MoveNext();

Upvotes: -1

Justin Balvanz
Justin Balvanz

Reputation: 1186

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

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)

Upvotes: 14

Welbog
Welbog

Reputation: 60388

You want the CASE statement:

SELECT
  CASE 
    WHEN @SelectField1 = 1 THEN Field1
    WHEN @SelectField2 = 1 THEN Field2
    ELSE NULL
  END AS NewField
FROM Table

EDIT: My example is for combining the two fields into one field, depending on the parameters supplied. It is a one-or-neither solution (not both). If you want the possibility of having both fields in the output, use Quassnoi's solution.

Upvotes: 37

l3a0
l3a0

Reputation: 59

@selectField1 AS bit
@selectField2 AS bit

SELECT
CASE 
    WHEN @selectField1 THEN Field1
    WHEN @selectField2 THEN Field2
    ELSE someDefaultField
END
FROM Table

Is this what you're looking for?

Upvotes: 3

waqasahmed
waqasahmed

Reputation: 3825

what you want is:

    MY_FIELD=
        case 
            when (selectField1 = 1) then Field1
                                 else Field2        
        end,

in the select

However, y don't you just not show that column in your program?

Upvotes: 0

Daniel A. White
Daniel A. White

Reputation: 190907

This is a psuedo way of doing it

IF (selectField1 = true) 
SELECT Field1 FROM Table
ELSE
SELECT Field2 FROM Table

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425261

In SQL, you do it this way:

SELECT  CASE WHEN @selectField1 = 1 THEN Field1 ELSE NULL END,
        CASE WHEN @selectField2 = 1 THEN Field2 ELSE NULL END
FROM    Table

Relational model does not imply dynamic field count.

Instead, if you are not interested in a field value, you just select a NULL instead and parse it on the client.

Upvotes: 84

Related Questions