SQL Case statement

[jnavarro]

I would like to use a case statement to determine the day of the week i.e. Sun =1 Mon = 2 etc. The information is being supplied from a SP. The user will type in the day of the week (mon,tue, etc) and I want the SP to determine the numeric value. I have provided my SP.


CREATE Procedure TypicalInsert @AssocNum bigint,@DOW nvarchar(3),@ShiftStart datetime,@ShiftEnd datetime,@LunchO datetime,@LunchI datetime
as
set @lunchI=null
set @lunchO=null

Insert into tblTypical (AssocNum,DOW,ShiftStart,ShiftEnd,LunchO,LunchI)

values (@AssocNum,@DOW,@shiftstart,@ShiftEnd,@LunchO,@LunchI)
GO


I would like to use the variable @DOW to insert the numeric value. Does anyone know how this can be done.

 

[SQLSister]

Why not just send the numeric value? I do this all the time for things like this where I can use a combo box on the form. It shows the words but uses the number as the value it sends to record.

Questions about posting. See faq183-874

 

[donutman]

This is where I prefer to use the actual text value. Why store a number so that you can then turn around and convert it back to text. I know that you could make a case that you might have to sort on the number, but that can be solved otherwise.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jnavarro]

The information is being typed in the directly into the SQL and also vb. I would like to use combobox however, I cannot correct information entered in the SP. Also, I would like to stored both values for sorting purposes.


Thanks

 

[donutman]

You can sort on ShiftStart or ShiftEnd. If the information is wrong coming in then the conversion of DOW to a number isn't going to work either.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jnavarro]

I trying writing a trigger however I am receiving an error around the Case. Can someone help me in figuring this out.

CREATE TRIGGER Typical ON tblTypical
for Insert
as
UPDATE tblTypical
CASE DOW
WHEN 'Sun' THEN SET segment = 1
WHEN 'Mon' THEN SET segment = 2
WHEN 'Tue' THEN SET segment = 3
WHEN 'Wed' THEN SET segment = 4
WHEN 'Thu' THEN SET segment = 5
WHEN 'Fri' THEN SET segment = 6
WHEN 'Sat' THEN SET segment = 7
END
GO

 

[dbomrrsm]

CREATE TRIGGER Typical ON tblTypical
for Insert
as
UPDATE tblTypical
SET segment = 
    CASE DOW
        WHEN 'Sun' THEN 1
        WHEN 'Mon' THEN 2
        WHEN 'Tue' THEN 3
        WHEN 'Wed' THEN 4
        WHEN 'Thu' THEN 5
        WHEN 'Fri' THEN 6
        WHEN 'Sat' THEN 7
     END
GO

[blue]DBomrrsm[/blue]

 

[jnavarro]

Thanks