Click here to Skip to main content
15,901,505 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
ALTER PROCEDURE [dbo].[uspGetBusesByDepature] 
	-- Add the parameters for the stored procedure here
	@vRouteNumber varchar(4),
	@dtDepartureTime datetime,
	@dtArrivalTime datetime,
	@InDate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @DeparturePoint geography,
			@Latitude float,
			@Longitude float,
			@Radius float

    -- Insert statements for procedure here
	SET @Latitude = (SELECT fRouteWayPointLatitude 
					 FROM dbo.RouteWayPoint 
					 WHERE vRouteNumber = @vRouteNumber 
							AND iSequence = 1)

	SET @Longitude = (SELECT fRouteWayPointLongitude
					 FROM dbo.RouteWayPoint 
					 WHERE vRouteNumber = @vRouteNumber 
							AND iSequence = 1)

	SET @Radius = (SELECT fRouteWayPointRadius
					 FROM dbo.RouteWayPoint 
					 WHERE vRouteNumber = @vRouteNumber 
							AND iSequence = 1) * 1000

	SET @DeparturePoint = geography::Point(@Latitude, @Longitude, 4326)
	SET @dtDepartureTime = CONVERT(varchar, @InDate, 101) + ' ' + CONVERT(varchar(8), @dtDepartureTime, 108)

	SELECT gps.iVehicleID, sDesc AS vVehicleDescription, sRegNo AS [vVehicleRegNo], 
			 SUBSTRING(CONVERT(varchar(8), MIN(dtTime), 108), 1, 5) 
			 + ' | ' + SUBSTRING(sDesc, 1, 4) + ' - ' + 
			 SUBSTRING(sRegNo, 1, CHARINDEX('-',sRegNo,1)-1) 
			AS vVehicleText, MIN(dtTime) AS dtTime--, 
			--dbo.fnBusIsAssigned(gps.iVehicleID, @dtDepartureTime, @dtArrivalTime, @InDate) AS isAssigned
	FROM dbo.GPSDataDW gps INNER JOIN Vehicles v
	ON gps.iVehicleID = v.iVehicleID
	WHERE dtTime BETWEEN  DATEADD(MI, -30, @dtDepartureTime) AND DATEADD(MI, 30, @dtDepartureTime) 
		AND @Radius >  @DeparturePoint.STDistance(geography::Point(fLatitude, fLongitude, 4326))
	GROUP BY gps.iVehicleID, sDesc, sRegNo

	END


I don't seem to be returning multiple tables at once can someone help me.
Posted
Comments
ZurdoDev 1-Oct-13 8:10am    
1. What does LINQ have to do with this?
2. You only have 1 Select statement therefore you only have 1 table.
3. What are you trying to do?

1 solution

Add
SQL
SET FMTONLY OFF


after
SQL
SET NOCOUNT ON;
 
Share this answer
 
Comments
Govindaraj Rangaraj 1-Oct-13 9:04am    
After this delete the linqtosql method for this SP and drag and drop afresh to get the auto generated return type.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900