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...
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
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 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.
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.
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)
@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?
This is a psuedo way of doing it
IF (selectField1 = true)
SELECT Field1 FROM Table
ELSE
SELECT Field2 FROM Table
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?
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();
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
