-
May 12th, 2005, 02:05 PM
#1
Thread Starter
Frenzied Member
[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.
-
May 12th, 2005, 07:16 PM
#2
Frenzied Member
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
-
May 13th, 2005, 06:34 AM
#3
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.
-
May 13th, 2005, 10:53 PM
#4
Thread Starter
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|