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 TableThanks Karl
7 Answers
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 TableRelational 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.
You want the CASE statement:
SELECT CASE WHEN @SelectField1 = 1 THEN Field1 WHEN @SelectField2 = 1 THEN Field2 ELSE NULL END AS NewField
FROM TableEDIT: 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.
3Sounds 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
ENDDynamic 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)@selectField1 AS bit
@selectField2 AS bit
SELECT
CASE WHEN @selectField1 THEN Field1 WHEN @selectField2 THEN Field2 ELSE someDefaultField
END
FROM TableIs this what you're looking for?
This is a psuedo way of doing it
IF (selectField1 = true)
SELECT Field1 FROM Table
ELSE
SELECT Field2 FROM Tablewhat 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?
The noob way to do this:
SELECT field1, field2 FROM table WHERE field1 = TRUE OR field2 = TRUEYou 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();
