CODEWITHSUNDEEP
sqlsql-servert-sql
Data_x99
Data_x99

Reputation: 39

Can I use column name as a parameter in case statement

I want to use the column name's value as a parameter in a case statement.

The column names I have are numbers and I want to do some calculations on the column name and return [value] or [null] depending on the results.

I am doing this in a view on SQL Server.

so for example, lets say 2013 is the name of the column and year([some date]) = 2016 then [New Column] should equal to 0.

SELECT CASE WHEN [2013] < year([some date]) then 0 else [2013] END AS [New Column]

Upvotes: 0

Views: 1724

Answers (3)

Steef
Steef

Reputation: 333

Continuing on Zohar Peled's roundup, I can think of a way. Especially since you also selected the tag tsql...

declare @colname nvarchar(4) = '2013'
declare @statement nvarchar(4000) = N'SELECT CASE WHEN ' + @colname + N' < year([some date]) then 0 else [' + @colname + N'] END AS [New Column]'
exec sp_executesql @statement

I have to admit though, I do not understand (either) what you are trying to do... This way you have to hard-code the column names anyway, which raises the question why you didn't do that from the start.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82504

Based on the comments to the question, you're looking for something like this (Pseudo code - Will not work!):

SELECT CASE WHEN CAST(NAME_OF([2013]) AS INT) < year([some date]) THEN 0 ELSE [2013] END AS [New Column]
FROM [TableName]

Where the pseudo function NAME_OF returns the name of the object.
However, SQL Server does not contain such a function (It does have object_name, but that takes in an int that represents the object's id, and to get the object id you need to know the object's name, so that doesn't help very much, and anyway table columns don't have an object id, their id is a combination of their containing table id and their column id).

You could do something like this, though:

SELECT CASE WHEN 2013 < year([some date]) THEN 0 ELSE [2013] END AS [New Column]
FROM [TableName]

That would return 0 when [some date] is before 2013, or the value stored in the column named 2013 otherwise.

Upvotes: 0

BenBITDesign
BenBITDesign

Reputation: 101

Yes this is possible: T-SQL documentation about case statements

Naming your column name as a number looks a bit dirty though, I would advise against that. That might actually be your issue. See this answer

Upvotes: 1

Related Questions