Click here to Skip to main content
15,042,088 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everybody,
I wrote following stored procedure using dynamic sql..It throws error like the parameterized query '(@R1 NVARCHAR(20),@R2 nvarchar(20),@R3 date,@R4 datetime,@R5 flo' expects the parameter '@R19', which was not supplied.

The sp is as follows:

ALTER PROCEDURE [dbo].[Usp_InsertGPSDataSample]
-- Add the parameters for the stored procedure here
@ime_Number NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY

DECLARE @execquery AS NVARCHAR(MAX),
@tablename AS NVARCHAR(20),
@result AS INT

SET @tablename=@ime_Number

SET @execquery='INSERT INTO '+@tablename+' (Device_status,Device_Date,Device_Time,Latitude,Longitude,Distance,Altitude,Angle,Speed,Ignition,Io2,Io3,Io4,Fuel,Analog_data,Serial_data,Mode,Movement)'

SET @execquery=@execquery+'VALUES(@R1,@R2,@R3,@R4,@R5,@R6,@R7,@R8,@R9,@R10,@R11,@R12,@R13,@R14,@R15,@R16,@R17,@R18,@R19)'

EXEC @result=sp_executesql @execquery,N'@R1 NVARCHAR(20),@R2 nvarchar(20),@R3 date,@R4 datetime,@R5 float,@R6 float,@R7 float,@R8 float,@R9 float,
@R10 float,@R11 int,@R12 int,@R13 int,@R14 int,@R15 float,@R16 float,@R17 float,@R18 int,@R19 int',

'@A','2014-01-24','10:06:38',68.545,46.879,78,454,6,4,7,3,6,1,1,75,22,3,-1

RETURN @result


END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrMsg, ERROR_LINE() AS ErrLine,ERROR_PROCEDURE() AS ErrProc
END CATCH

END

Any help will be appreciated!!!

THANK YOU
Posted
Updated 30-Jan-14 1:54am
v3
Comments
thatraja 30-Jan-14 5:59am
   
What's the error?

@gps is a variable: this does not include the value in the variable, it includes the name of it:
SQL
SET @execquery='INSERT INTO' +@tablename+
'SELECT device_status,CAST(device_date AS VARCHAR(50)),CAST(device_time AS VARCHAR(50)),latitude,longitude,distance,altitude,angle,speed,ignition,io2,io3,io4,fuel,analog_data,serial_data,mode,movement FROM @gps'
Try:
SQL
SET @execquery='INSERT INTO' +@tablename+
'SELECT device_status,CAST(device_date AS VARCHAR(50)),CAST(device_time AS VARCHAR(50)),latitude,longitude,distance,altitude,angle,speed,ignition,io2,io3,io4,fuel,analog_data,serial_data,mode,movement FROM ' + @gps
   
Comments
Member 10017719 30-Jan-14 6:11am
   
Thanks OriginalGriff.....Error like must declare scalar variable @gps..
query is not well formatted.
try this.

SQL
SET @execquery='INSERT INTO ' +@tablename+
' SELECT device_status,CAST(device_date AS VARCHAR(50)),CAST(device_time AS VARCHAR(50)),latitude,longitude,distance,altitude,angle,speed,ignition,io2,io3,io4,fuel,analog_data,serial_data,mode,movement FROM @gps'
   
Comments
Member 10017719 30-Jan-14 6:08am
   
Thanks Karthick for ur reply but still same issue..Query is executing but values have'nt inserted in table
Karthik_Mahalingam 30-Jan-14 6:49am
   
ok. do like this
print the @execquery and check the string.
and try to execute it manually on the sql server. and check it is working or not?
Member 10017719 30-Jan-14 7:55am
   
I checked manually also its executing but table does not contains values
Karthik_Mahalingam 30-Jan-14 10:11am
   
how did u try manually?
with @gps ??
Siva Hyderabad 30-Jan-14 8:55am
   
http://www.codeproject.com/Questions/718478/how-to-set-default-select-in-dropdown-list-using-r

see this link please
Table Name should not be numeric
   

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