August 19, 2013 at 2:29 pm
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
August 19, 2013 at 2:44 pm
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
August 20, 2013 at 5:58 am
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best
practices on asking questions.
August 20, 2013 at 6:12 am
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)
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
August 20, 2013 at 6:18 am
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
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
August 20, 2013 at 6:43 am
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
August 20, 2013 at 6:46 am
Oh nevermind...lol
August 20, 2013 at 7:07 am
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