Dynamic Select Query fails

Question

0.00/5 (No votes)

See more:

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:

SQL

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

Posted 7-Feb-14 23:13pm

Member 10017719

Updated 7-Feb-14 23:25pm

Karthik_Mahalingam

v3

Add a Solution

Comments

abhijitmalkar 8-Feb-14 5:28am

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))
---------------------------- OR-------------------------------------------
SET @Latitude_Val='SELECT Latitude FROM dbo.'+@Imei_Table+' WHERE Imei_Id= '+CONVERT(VARCHAR(50),@Imei_PK)

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

THERE IS AT LEAST ONE SPACE IS REQUIRED WITH IN TABLE NAME AND WHERE CLAUSE

Member 10017719 8-Feb-14 5:53am

Thank u abhijitmalkar but still its returning null for lat and long....

2 solutions

Solution 1

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

SQL

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.

Posted 7-Feb-14 23:29pm

Karthik_Mahalingam

Comments

Member 10017719 8-Feb-14 5:38am

Thank u so much karthik.But still its returning null

Karthik_Mahalingam 8-Feb-14 5:57am

get the query from this print @Latitude_Val and try to run it..
if it is successfuly then the dynamic query is fine.. else it has some issue...

Karthik_Mahalingam 8-Feb-14 5:58am

SELECT @Imei_Number= Top 1 Imei_No FROM MapDevice_Vehicle WHERE Vehicle_No=@reg_Number;

Member 10017719 8-Feb-14 6:08am

I m getting Imei_Number Karthick.Thats no probs

Karthik_Mahalingam 8-Feb-14 6:11am

exec ( @Latitude_Val)
exec (@Longitude_Val)

Member 10017719 8-Feb-14 6:12am

I did tat also plz see SP

Karthik_Mahalingam 8-Feb-14 6:17am

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)) + ''''

Karthik_Mahalingam 8-Feb-14 6:18am

i am going out now.. response will be delayed,
will reply after i am back..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 10017719 · Accepted Answer · 2014-04-10T02:31:00

Imei_Id should be selected from table..

SQL

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+')';