Emel Uras Emel Uras -5 years ago 71
SQL Question

How to write an attribute name to the select query dynamically

I have a table including:

ID Name Period0Id Period1Id Period2Id

What I would like to receive data based on a user-defined parameter @check.

Lets assume:

declare @check int = 1;

In this case I need to get Period1Id value from the table. So I need to have something like that:

Select ID, Name, StatusId = Period + @check + Id -- @check is the parameter
From mytable

However, my query is not working. How can I fix this?

Answer Source

Your table looks like it is not in first normal form.

Instead of three columns for Period0Id to Period2Id you could have a column for PeriodIndex with values of (0,1,2) and a single column for PeriodId and then it would be just a WHERE PeriodIndex = @Check

You can't select a column using string interpolation with a variable as you are attempting. You can use dynamic SQL to create the SQL String dynamically. Or simply hardcode the options if they all have the same dataype.

Select ID, 
       StatusId = CASE @Check WHEN 0 THEN Period0Id
                              WHEN 1 THEN Period1Id
                              WHEN 2 THEN Period2Id
From mytable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download