Getting the right sysntax for this.

[Rudyvb08]

Hello all! This seems pretty simple, but I'm still stuck. Probably making it more complicated than it is. I have a tblPot, with Monday, Tuesday, etc as Columns. I need to return the Pot of the week based on todays date. I'm getting the day of the week from SQL with the DATENAME function. But how can I pass that to match the column name. Basicly I need this simple statement. SELECT Potname FROM tblPot WHERE Monday = 'true' How can I get it so it grabs the current day automaticly. Thanks! Rudy :-D here is the code

ALTER PROCEDURE [dbo].[SchedPod]
-- Add the parameters for the stored procedure here

@Day nvarchar(50) Output,
@PotList nvarchar(50) OUTPUT

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

select @Day = DATENAME(dw , getDate())

SELECT @Potlist =  PotName

FROM tblPot
WHERE (@Day = 'true')

END

[Edbert P]

Unfortunately the table definition you have makes it quite difficult to get the result you want based on the current day. However, after saying that there is still a solution to use UNPIVOT. The idea is to change your day columns (Monday, Tuesday) to a row value, then query the result of the UNPIVOT. See UNPIVOT[^]

"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin Edbert Sydney, Australia

[Mycroft Holmes]

Use a case statement

Select @PotName = Case DATENAME(dw , getDate()) When 1 then MondayCol
when 2 then TusedayCol End
from tblPot