Need help with my tsql code

Question

Need help with my tsql code

dndaughtery

SSChampion

Points: 11290
More actions
August 19, 2013 at 2:29 pm

#281357

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

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

ParulPrabu SSCrazy Points: 2271 More actions · Answer 2

Need help? Help us help you.

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

practices on asking questions.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

dndaughtery SSChampion Points: 11290 More actions · Answer 5

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

dndaughtery SSChampion Points: 11290 More actions · Answer 6

dndaughtery

SSChampion

Points: 11290

August 20, 2013 at 6:46 am

#1643048

Oh nevermind...lol

dndaughtery SSChampion Points: 11290 More actions · Answer 7

dndaughtery

SSChampion

Points: 11290

August 20, 2013 at 7:07 am

#1643059

Thanks Chris that did the trick