Linq to sql unkown返回类型存储过程 [英] Linq to sql unkown return types stored procedure

问题描述

  ALTER   PROCEDURE  [dbo]。[uspGetBusesByDepature] 
   -   在此处添加存储过程的参数 
  @ vRouteNumber   varchar （ 4 ），
  @ dtDepartureTime   datetime ，
  @ dtArrivalTime  < span class =code-keyword> datetime ，
  @ InDate   datetime  
  AS  
  BEGIN  
   -   添加SET NOCOUNT ON以防止额外的结果集 
   -   干扰SELECT语句。 
  SET   NOCOUNT   ON ; 
 
  DECLARE   @ DeparturePoint  地理位置，
  @ Latitude   float ，
  @Longitude   float ，
  @ Radius   float  
 
   -   插入过程语句这里 
  SET   @ Latitude  =（ SELECT  fRouteWayPointLatitude 
  FROM  dbo.RouteWayPoint 
  WHERE  vRouteNumber =  @ vRouteNumber  
  AND  iSequence =  1 < /温泉n>）
 
  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 ），< span class =code-sdkkeyword> @ 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，@ divDepartureTime，@ 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 ））
 < span class =code-keyword> GROUP   BY  gps.iVehicleID，sDesc，sRegNo 
 
 结束 

我似乎没有立即返回多个表，有人可以帮助我。

解决方案

添加

  SET   FMTONLY   OFF  

  SET   NOCOUNT   ON ; 

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.

解决方案

Add

SET FMTONLY OFF

after

SET NOCOUNT ON;

这篇关于Linq to sql unkown返回类型存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！