我想将devicenumber作为参数传递到我的proc中,我必须按设备号从devicemaster表中选择deviceid,从这个deviceid中我必须从卡车主站选择RegistrationNumber和currentlocation,从tripdetails (这两个表deviceid都是常见的)中选择和currentlocation(这两个表deviceid都是常见的),但是从tocityid和tocityid有一个城市名,它存在于区域主表中。
因此,通过将DeviceNUmber作为参数传递,我希望结果集具有列RegistrationNumber、当前位置、距离、fromcityname和tocityname。
我得到了这个错误:
Msg 209,第16级,状态1,过程PROC_GMAPSDATA,第24行模糊列名'DEVICEID‘。
程序将如何进行:
ALTER PROCEDURE [dbo].[PROC_GMAPSDATA]
(
@DEVICENUMBER varchar(50)
)
AS
BEGIN
DECLARE @DEVICEID INT
SET @DEVICEID=(SELECT DEVICEID FROM DEVICEMASTER WHERE DEVICENO= @DEVICENUMBER)
SELECT TM.REGISTRATIONNUMBER ,
TM.CURRENTLOCATION,
TM.CURRENTLATLNG,
RM.CityName AS FromCity,
RM1.CityName AS ToCity,
TD.DISTANCE
FROM TRUCKMASTER TM
JOIN TRIPDETAIL TD ON TM.DEVICEID=TD.DEVICEID
JOIN REGIONMASTER RM ON RM.REGIONID =TD.FROMCTYID
JOIN REGIONMASTER RM1 ON RM1.REGIONID=TD.TOCTYID
WHERE DEVICEID IN (SELECT DEVICEID FROM DEVICEMASTER WHERE DEVICENO= @DEVICENUMBER)
END
GO发布于 2015-11-26 07:29:37
我希望您的错误消息来自于WHERE DEVICEID IN ...,其中您没有列的别名。查询不知道您的意思是TM.DEVICEID还是TD.DEVICEID,所以如果将最后一行更改为下面的行,应该可以:
WHERE TM.DEVICEID IN (SELECT DEVICEID FROM DEVICEMASTER WHERE DEVICENO = @DEVICENUMBER)发布于 2015-11-26 07:42:32
我已将您的查询格式化如下。别名包括在Where条款中。始终确保使用在查询中定义的变量。
ALTER PROCEDURE [dbo].[PROC_GMAPSDATA]
(
@DEVICENUMBER varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DEVICEID INT
SET @DEVICEID=(SELECT DEVICEID FROM DEVICEMASTER WHERE DEVICENO= @DEVICENUMBER)
SELECT TM.REGISTRATIONNUMBER ,
TM.CURRENTLOCATION,
TM.CURRENTLATLNG,
RM.CityName AS FromCity,
RM1.CityName AS ToCity,
TD.DISTANCE
FROM TRUCKMASTER TM
INNER JOIN TRIPDETAIL TD
ON TM.DEVICEID = TD.DEVICEID
INNER JOIN REGIONMASTER RM
ON RM.REGIONID = TD.FROMCTYID
INNER JOIN REGIONMASTER RM1
ON RM1.REGIONID = TD.TOCTYID
WHERE TM.DEVICEID = @DEVICEID;
SET NOCOUNT OFF;
ENDhttps://stackoverflow.com/questions/33931903
复制相似问题