gpalmer711
IS-IT--Management
This seems to be something that should be easy - however i've been googling this for ages and cannot find anything similar.
This is a continuation of thread183-1338691
I'm now trying to add another feature which will basically calculate whether the venue is open or not. I have this working fine within asp.net.
However what I would like to do is add another column to the data that is returned from the stored procedure that can be found in the code box below.
So what I would like to do is change this line
to
What I then want is the fnCalculateOpen to return the day of week and hour of the day, that is then checked to see if that venue is open on that day and time. Again I have the code for that working.
This returns a single column and single row of data, I know that it will always return a single row and column of data.
What I cannot work out is how to get that single cell of data back to the original Stored Procedure.
I would really appreciate it if someone could point me in the right direction as it is driving me gaga.
Greg Palmer
Freeware Utilities for Windows Administrators.
This is a continuation of thread183-1338691
I'm now trying to add another feature which will basically calculate whether the venue is open or not. I have this working fine within asp.net.
However what I would like to do is add another column to the data that is returned from the stored procedure that can be found in the code box below.
Code:
USE [searchforamenu]
GO
/****** Object: StoredProcedure [dbo].[RetrieveVenues] Script Date: 04/14/2007 01:42:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[RetrieveVenues]
@userlat decimal(9,6),
@userlong decimal(9,6),
@distance int = 5,
@takeaway bit = 0,
@restaurant bit = 0,
@pub bit = 0,
@cafe bit = 0,
@others bit = 0,
@alcohol bit = 0,
@playarea bit = 0,
@pizza bit = 0,
@chinese bit = 0,
@indian bit = 0,
@friedchicken bit = 0,
@sandwiches bit = 0,
@mexican bit = 0,
@fish bit = 0,
@burgers bit = 0,
@babychange bit = 0,
@bottlewarming bit = 0,
@vegetarian bit = 0,
@vegan bit = 0,
@delivery bit = 0,
@desserts bit = 0,
@seafood bit = 0,
@glutenfree bit = 0,
@halal bit = 0,
@kosher bit = 0,
@italian bit = 0,
@japanese bit = 0
as
Select *,
convert(decimal(4,2), dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)) as VenueDistance
From menu_venues
Where dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude) < @distance
And
(@TakeAway = 0 or [takeaway] = @takeaway)
And
(@restaurant = 0 or [restaurant] = @restaurant)
And
(@pub = 0 or [pub] = @pub)
And
(@cafe = 0 or [cafe] = @cafe)
And
(@others = 0 or [other] = @others)
And
(@alcohol = 0 or [alcohol] = @alcohol)
And
(@playarea = 0 or [playarea] = @playarea)
And
(@pizza = 0 or [pizza] = @pizza)
And
(@chinese = 0 or [chinese] = @chinese)
And
(@indian = 0 or [indian] = @indian)
And
(@friedchicken = 0 or [friedchicken] = @friedchicken)
And
(@sandwiches = 0 or [sandwiches] = @sandwiches)
And
(@mexican = 0 or [mexican] = @mexican)
And
(@fish = 0 or [fish] = @fish)
And
(@burgers = 0 or [burgers] = @burgers)
And
(@babychange = 0 or [babychange] = @babychange)
And
(@bottlewarming = 0 or [bottlewarming] = @bottlewarming)
And
(@vegetarian = 0 or [vegetarian] = @vegetarian)
And
(@vegan = 0 or [vegan] = @vegan)
And
(@delivery = 0 or [delivery] = @delivery)
And
(@desserts = 0 or [desserts] = @desserts)
And
(@seafood = 0 or [seafood] = @seafood)
And
(@glutenfree = 0 or [glutenfree] = @glutenfree)
And
(@halal = 0 or [halal] = @halal)
And
(@kosher = 0 or [kosher] = @kosher)
And
(@italian = 0 or [italian] = @italian)
And
(@japanese = 0 or [japanese] = @japanese)
Order By dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)So what I would like to do is change this line
Code:
Select *,
convert(decimal(4,2), dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)) as VenueDistanceto
Code:
Select *,
convert(decimal(4,2), dbo.fnCalculateDistance(@userlong, @userlat, menu_venues.longitude, menu_venues.latitude)) as VenueDistance, dbo.fnCalculateOpen(@venueID) as VenueOpenWhat I then want is the fnCalculateOpen to return the day of week and hour of the day, that is then checked to see if that venue is open on that day and time. Again I have the code for that working.
Code:
Declare @Day int
Set @Day = (DATEPART(dw, GETDATE()) + @@DATEFIRST + 5) % 7 + 1
Declare @mySQL varchar(100)
set @mySQL = 'select [' + convert(varchar(2),DATENAME (hour,GETDATE())) + '] from [menu_opening_hours] where [venue_ID] = ' + convert(varchar, @venueID) + 'AND day_of_week = ''' + convert(varchar(1),@Day) + ''''
exec(@mySQL)This returns a single column and single row of data, I know that it will always return a single row and column of data.
What I cannot work out is how to get that single cell of data back to the original Stored Procedure.
I would really appreciate it if someone could point me in the right direction as it is driving me gaga.
Greg Palmer
Freeware Utilities for Windows Administrators.
I will be doing a SQL course to try and get to grips with this side of things.![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
