[RESOLVED] SQL Server - Using variable as Column Name and WHERE clause-VBForums
Results 1 to 4 of 4

Thread: [RESOLVED] SQL Server - Using variable as Column Name and WHERE clause

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2000
    Location
    Birmingham, AL
    Posts
    1,276

    Resolved [RESOLVED] SQL Server - Using variable as Column Name and WHERE clause

    I have a SQL Server table with fields named Monday, Tuesday,...Sunday

    I want to find the day of the week, use that as the field name, and only return records where the field is set to 1.

    I have a stored Procedure:
    Code:
    CREATE PROCEDURE [dbo].[proc_GetScheduledJobs] AS
    
    	DECLARE @dayOfWeek VARCHAR(32)
    
    	SET @dayOfWeek = DATENAME(WEEKDAY, GETDATE())
    
    	SELECT @dayOfWeek FROM Table1 WHERE @dayOfWeek=1
    
    	/*SELECT Thursday FROM Table1 WHERE Thursday=1*/
    GO
    and the error is:
    Server: Msg 245, Level 16, State 1, Procedure proc_GetScheduledJobs, Line 7
    Syntax error converting the varchar value 'Thursday' to a column of data type int.

    since today is Thursday.

    I tried adding '[' + ']' around the variables but that didn't work.
    Last edited by wey97; May 13th, 2005 at 10:54 PM.

  2. #2
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313

    Re: SQL Server - Using variable as Column Name and WHERE clause

    Sorry. No can do. You'll need to use dynamic SQL.
    Something like this:
    Code:
    CREATE PROCEDURE [dbo].[proc_GetScheduledJobs] AS
    
    	DECLARE @dayOfWeek VARCHAR(32)
    
    	SET @dayOfWeek = DATENAME(WEEKDAY, GETDATE())
    
    	exec('SELECT ' + @dayOfWeek + ' FROM Table1 WHERE ' + @dayOfWeek + '=1')
    
    	/*SELECT Thursday FROM Table1 WHERE Thursday=1*/
    GO

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: SQL Server - Using variable as Column Name and WHERE clause

    You could also use CASE WHEN...

    Code:
    SELECT CASE @DAYOFWEEK WHEN 'MONDAY' THEN MONDAYCOL 
        WHEN 'TUESDAY' THEN TUESDAYCOL
    
        ... and so on
    I would not use a text dayname - I would return the numeric dayname and check 1 through 7.

    That same CASE statement can sit in the SELECT and the WHERE clause.

    You will lose the ability to have an INDEX scan in the WHERE clause, so if this is important I would go with 7 IF/BEGIN/END statements - one for each day.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2000
    Location
    Birmingham, AL
    Posts
    1,276

    Re: SQL Server - Using variable as Column Name and WHERE clause

    The table I have has the fields Monday,Tuesday,...,Sunday and the field type is a bit. I'm running a schedule and the bit signifies a job will run on that day.

    What PilgrimPete suggested worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.