动态选择查询失败 [英] Dynamic Select Query fails

问题描述

Hello Everybody，



我用动态选择查询写了sp。 Sp编译成功，但在执行纬度和经度值时返回null ..



存储过程如下：

Hello Everybody,

I wrote sp using dynamic select Query. Sp compiled successfully, but while executing latitude and longitude values returning null..

The Stored Procedure is as follows:

ALTER PROCEDURE [dbo].[Usp_TrackVehicleDetails] 
	 
			@reg_Number     NVARCHAR(25)
	 
AS
BEGIN

    DECLARE	        @Imei_Number    NVARCHAR(25),
			@Imei_Table	NVARCHAR(MAX),
			@Latitude_Val	NVARCHAR(MAX),
			@Longitude_Val  NVARCHAR(MAX),
			@Imei_PK		INT,
			@dt				DATETIME
			
	 -- Tracking Device by means of Latitude and Longitude
	 
			SELECT @Imei_Number=Imei_No FROM MapDevice_Vehicle WHERE Vehicle_No=@reg_Number;
			
			SELECT @Imei_Table='Imei_'+@Imei_Number
			
			SET @Imei_PK=SCOPE_IDENTITY();
	 
			SET @Latitude_Val='SELECT Latitude FROM dbo.'+@Imei_Table+'WHERE Imei_Id='+CAST(@Imei_PK AS VARCHAR(50))
			
			SET @Longitude_Val='SELECT Longitude FROM dbo.'+@Imei_Table+'WHERE Imei_Id='+CAST(@Imei_PK AS VARCHAR(50))
			
			EXECUTE sp_executesql @Latitude_Val,@Longitude_Val; 
				
				   				   
		    BEGIN TRY
		    
				BEGIN TRANSACTION T1
				
	 -- Returning Values based on Vehicle_Number
	  
			SELECT ISNULL(@Latitude_Val,'')[latitude],
				   ISNULL(@Longitude_Val,'')[longitude],
				   ISNULL(@dt,GETDATE())[dateTime]
				
				IF @@ERROR!=0
				BEGIN
					ROLLBACK TRANSACTION T1
				END
				ELSE			
																									
				COMMIT TRANSACTION T1 
								
			END TRY
						
			BEGIN CATCH
				SELECT ERROR_MESSAGE() AS ErrMsg, ERROR_LINE() AS ErrLine,ERROR_PROCEDURE() AS ErrProc
			END CATCH
END

推荐答案

在sql中生成动态查询时处理语法

在之前添加空格

take care of syntax while generating dynamic query in sql
add space before where

SET @Latitude_Val='SELECT Latitude FROM dbo.'+@Imei_Table+' WHERE Imei_Id='+CAST(@Imei_PK AS VARCHAR(50))

            SET @Longitude_Val='SELECT Longitude FROM dbo.'+@Imei_Table+'  WHERE Imei_Id='+CAST(@Imei_PK AS VARCHAR(50))

print @Latitude_Val  -- check this value and try to run separately for validating.
print @Longitude_Val-- check this value and try to run separately for validating.

Imei_Id应从表中选择..

Imei_Id should be selected from table..

ELECT @Imei_Table='Imei_'+@Imei_Number

            SET @Imei_PK='SELECT(MAX(CAST(Imei_Id AS NVARCHAR(MAX)))) FROM dbo.'+@Imei_Table;
            SET @paramdefs = N'@Imei_PK nvarchar(255)';

            SELECT @LatLong_Val=N'SELECT Latitude,Longitude FROM dbo.'+@Imei_Table +' WHERE CAST(Imei_Id AS NVARCHAR(MAX))='+'('+@Imei_PK+')';

这篇关于动态选择查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！