Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with my tsql code Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 2:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 342, Visits: 763
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

Post #1486003
Posted Monday, August 19, 2013 2:44 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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 at GMail
Post #1486010
Posted Tuesday, August 20, 2013 5:58 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best
practices on asking questions.
Post #1486203
Posted Tuesday, August 20, 2013 6:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1486211
Posted Tuesday, August 20, 2013 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1486217
Posted Tuesday, August 20, 2013 6:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 342, Visits: 763
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
Post #1486223
Posted Tuesday, August 20, 2013 6:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 342, Visits: 763
Oh nevermind...lol
Post #1486225
Posted Tuesday, August 20, 2013 7:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 342, Visits: 763
Thanks Chris that did the trick
Post #1486236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse