Need help with my tsql code

  • I have the following query where I need to set a variable to the value of a column in a row in a table. But depending on the circumstances which column to set it equal to. My code follows and executes but it just keeps running:

    DECLARE @DayOfWeek Tinyint

    DECLARE @OrderEntryDate datetime

    DECLARE @retval Tinyint

    DECLARE @retvalOUT TinyInt

    DECLARE @ParmDefinition nvarchar(500);

    SET @ParmDefinition = N'@retvalOUT int OUTPUT'

    Set @OrderEntryDate = '01/04/2012'

    SET @DayOfWeek = 1

    DECLARE @TSQLQuery nvarchar(max)

    SET @TSQLQuery =

    CASE @DayOfWeek

    WHEN 1 THEN N'Select @retvalOUT = Convert(TinyInt,Sunday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    WHEN 2 THEN N'Select @retvalOUT = Convert(TinyInt,Monday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    WHEN 3 THEN N'Select @retvalOUT = Convert(TinyInt,Tuesday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    WHEN 4 THEN N'Select @retvalOUT = Convert(TinyInt,Wednesday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    WHEN 5 THEN N'Select @retvalOUT = Convert(TinyInt,Thursday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    WHEN 6 THEN N'Select @retvalOUT = Convert(TinyInt,Friday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    WHEN 7 THEN N'Select @retvalOUT = Convert(TinyInt,Saturday) from [dbo].[SLADCSchedule] where ScheduleStart <= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and ScheduleEnd >= ''' + Convert(varchar(50),@OrderEntryDate) + ''' and BranchID = 99'

    --ELSE 'Not for sale'

    END

    --Print @TSQLQuery

    --Set @DayCounter = @DayCounter +

    --EXECUTE(@TSQLQuery)

    exec sp_executesql @TSQLQuery, N'@retvalOUT Tinyint OUTPUT', @retvalOUT = @retval OUTPUT;

    print @retval

  • Please provide table create script(s) (with indexes if any), sample data to put in said table(s), and expected output from your code.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best

    practices on asking questions.

  • Here's the query generated by your very strange code:

    Select @retvalOUT = Convert(TinyInt,Sunday)

    from [dbo].[SLADCSchedule]

    where ScheduleStart <= 'Apr 1 2012 12:00AM'

    and ScheduleEnd >= 'Apr 1 2012 12:00AM'

    and BranchID = 99

    What's this supposed to do?

    Convert(TinyInt,Sunday)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Never mind. Try this:

    SELECT @retvalOUT = CONVERT(TINYINT,x.OutputColumn)

    FROM [dbo].[SLADCSchedule]

    CROSS APPLY (

    SELECT OutputColumn =

    CASE @DayOfWeek

    WHEN 1 THEN Sunday

    WHEN 2 THEN Monday

    WHEN 3 THEN Tuesday

    WHEN 4 THEN Wednesday

    WHEN 5 THEN Thursday

    WHEN 6 THEN Friday

    WHEN 7 THEN Saturday

    END

    ) x

    WHERE ScheduleStart <= @OrderEntryDate

    AND ScheduleEnd >= @OrderEntryDate

    AND BranchID = 99

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I'm not trying to return the name of a day. In my query I'm trying to return the boolean value of columns named "Monday", "Tuesday"....etc. My table is

  • Oh nevermind...lol

  • Thanks Chris that did the trick

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply