CODEWITHSUNDEEP
sqlsql-server
user3022742
user3022742

Reputation: 113

Using a variable in a case statement, within a select statement

I am trying to use a variable in a select statement (SQL Server 2008) and I am having some difficulty. I've included my code below.

DECLARE @newFieldName nvarchar(30)

SET @newFieldName = 'Variable 1'

SELECT 

SUM(CASE WHEN @newFieldName IN ('x', 'y' ,'z') then 1 ELSE 0 END) as "Default",

SUM(CASE WHEN @newFieldName NOT IN ('x', 'y' ,'z') then 1 ELSE 0 END) as "Not Default"

FROM table 1

I don't get the correct results. However when I use the below, I do.

SELECT 

SUM(CASE WHEN [Variable 1] IN ('x', 'y' ,'z') then 1 ELSE 0 END) as "Default",

SUM(CASE WHEN [Variable 1] NOT IN ('x', 'y' ,'z') then 1 ELSE 0 END) as "Not Default"

FROM [table 1]

Any help would be much appreciated. Thanks.

Upvotes: 0

Views: 4024

Answers (2)

Leo
Leo

Reputation: 293

your @newFieldName is just a string: it does not "impersonate" the actual column in the table ...

I think that since your probably have only a handfull of columns which you are interested in, you could do something like 

DECLARE @newFieldName nvarchar(30)

SET @newFieldName = 'Variable 1'

SELECT
SUM(CASE WHEN @newFieldName = 'Variable 1' and [Variable 1] IN ('x', 'y' ,'z') then 1
         WHEN @newFieldName = 'Variable 2' and [Variable 2] IN ('x', 'y' ,'z') then 1 ELSE 0
    END) as [Default],
SUM(CASE WHEN @newFieldName = 'Variable 1' and [Variable 1] NOT IN ('x', 'y' ,'z') then 1
     WHEN @newFieldName = 'Variable 2' and [Variable 2] NOT IN ('x', 'y' ,'z') then 1 ELSE 0
END) as [Not Default],

FROM table 1

This way you avoid dynamic sql, but of course will get ugly quickly with more column possibilities

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

What is your question? The expression:

'Variable 1' IN ('x', 'y' ,'z')

Is always going to return FALSE because you haven't included the string 'Variable 1' in the list of accepted values.

The expression:

[Variable 1] IN ('x', 'y' ,'z')

will return TRUE when the variable contains one of those three values.

A constant string and the name of a column are two very different things. If you want to have code where you can change the name of a column with a variable, you need to learn about dynamic SQL and sp_executesql.

EDIT:

The dynamic SQL version of what you are doing is:

declare @sql nvarchar(max);
declare @newFieldName nvarchar(30);
set @newFieldName = 'Variable 1';

set @sql = '
SELECT SUM(CASE WHEN @newFieldName IN ('x', 'y' ,'z') then 1 ELSE 0 END) as "Default",
       SUM(CASE WHEN @newFieldName NOT IN ('x', 'y' ,'z') then 1 ELSE 0 END) as "Not Default"
FROM table 1';

set @sql = replace(@sql, '@newFieldName', '['+@newFieldName+']');

exec sp_executesql @sql;

However, you should start by reading the documentation on sp_executesql (here) and learning about dynamic SQL.

Upvotes: 3

Related Questions