SQL - use variable as column in SELECT statement and WHERE clause

Question

I'm trying to pull the students that are tardy for the previous period from our attendance database (SQL Server 2008). The period attendance is stored in ATT.A1, ATT.A2 ... ATT.A7. I want to schedule a job to run each hour, starting at 9am, and pull the tardy students, but I can't figure out the code.

Here's my code (pseudo-code):

Declare @Period varchar(6)
Set @Period = 'att.a' + Cast((DATENAME(hour, GETDATE()) - 8) as varchar(1))

Select SC, SN, DT, @Period as Period, ATT.A1
From ATT
Where SC = '9' and @Period = 'T' 
    and DT = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

When I use this, I get no results. If I remove @Period = 'T' from the Where clause, I get the following:

9   5177    2012-08-24 00:00:00.000 att.a1  T
9   5211    2012-08-24 00:00:00.000 att.a1   
9   5225    2012-08-24 00:00:00.000 att.a1  T
9   5229    2012-08-24 00:00:00.000 att.a1  T
9   5235    2012-08-24 00:00:00.000 att.a1  V
9   5242    2012-08-24 00:00:00.000 att.a1  T
9   5268    2012-08-24 00:00:00.000 att.a1

I know that when I use @Period in the SELECT statement and WHERE clause it's using the literal string value of @Period, but I need it to use the value of @Period as Table.Column.

So, at 9:00 it will select from ATT.A1, 10:00 from ATT.A2 ... 15:00 from ATT.A7 and each time compare whether ATT.A# = 'T'

I hope that's clear.

Thanks, Anthony

Using GetDate() within a query is chasing a moving target, impacts performance, and may produce curious results, e.g. as the date changes. It is almost always a better idea to capture the current date/time in a variable and then use that value as needed. This is more important across multiple statements as in a stored procedure. The most common reason to use GetDate() multiple times is when capturing the start and end times for a long running operation. — HABO
– HABO, Commented Aug 24, 2012 at 18:18
Could you provide sample input, i.e. ATT data, and the desired results? — HABO
– HABO, Commented Aug 24, 2012 at 18:20
@HABO Thank you. The Case Statement that Michael provided below works, but I changed it to apply what you talked about here, i.e. I Set @Date = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) and then used and DT = @Date in the Where clause of the actual query, so that it would only GETDATE() once. — Anthony Gattuso
– Anthony Gattuso, Commented Aug 24, 2012 at 21:26

Michael Fredrickson · Accepted Answer · 2012-08-24 18:24:10Z

2

Sql Server makes a distinction between a string containing a column name, and the column name itself, so you'll either need to use dynamic sql, or a case statement to translate the string to the actual column name as illustrated below:

Case Statement (I'd recommend this one):

Select SC, SN, DT, @Period as Period, ATT.A1
From ATT
Where 
    SC = '9' 
    and DT = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    and case @period
        when 'att.a1' then att.a1
        when 'att.a2' then att.a2
        when 'att.a3' then att.a3
        when 'att.a4' then att.a4
        when 'att.a5' then att.a5
        when 'att.a6' then att.a6
        when 'att.a7' then att.a7
    end = 'T'

Dynamic Sql:

Declare @sql varchar(max)
Set @sql = '
    Select SC, SN, DT, @Period as Period, ATT.A1
    From ATT
    Where SC = '9' and ' + @Period + ' = ''T'' 
        and DT = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)'
Exec(@sql)

edited Aug 24, 2012 at 18:24

answered Aug 24, 2012 at 18:18

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anthony Gattuso Over a year ago

The Case Statement you provided worked. Thank you for the help.

Collectives™ on Stack Overflow

SQL - use variable as column in SELECT statement and WHERE clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related