插入失败使用动态INSERT Sql

本文介绍了插入失败使用动态INSERT Sql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好， 我使用动态sql编写了以下存储过程。它抛出错误，如参数化查询'（@ R1 NVARCHAR（20），@ R2 nvarchar（20），@ R3 date ，@ R4 datetime，@ R5 flo'需要参数'@ R19'，这是未提供的。 sp如下： ALTER PROCEDURE [dbo]。[Usp_InsertGPSDataSample] - 在此处添加存储过程的参数 @ime_Number NVARCHAR（20 ） AS BEGIN 设定开启; 开始尝试 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，纬度，经度，距离，海拔，角度，速度，点火，Io2，Io3，Io4，燃料，肛门og_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 结束尝试 BEGIN CATCH SELECT ERROR_MESSAGE（）AS ErrMsg，ERROR_LINE（）AS ErrLine，ERROR_PROCEDURE（）AS ErrProc END CATCH END 任何帮助将不胜感激!!! 谢谢你解决方案

@gps 是一个变量：这不包括变量中的值，它包括名称它：

SET @ execquery = ' INSERT INTO' + @ tablename + ' SELECT device_status，CAST（device_date AS VARCHAR（50）），CAST（device_time AS VARCHAR（50）），纬度，经度，距离，高度，角度，速度，点火，io2，io3，io4，燃料，analog_data，serial_data，模式，运动FROM @gps'

尝试：

SET @ execquery = ' INSERT INTO' + @ tablename + SELECT device_status，CAST（device_date AS VARCHAR（50）），CAST（device_time AS VARCHAR（50）），纬度，经度，距离，高度，角度，速度，IG nition，io2，io3，io4，fuel，analog_data，serial_data，mode，movement FROM' + @ gps

查询格式不正确。 试试这个。

SET @execquery = ' INSERT INTO' + @ tablename + ' SELECT device_status，CAST（device_date AS VARCHAR（50）），CAST（ device_time AS VARCHAR（50）），纬度，经度，距离，高度，角度，速度，点火，io2，io3，io4，燃料，analog_data，serial_data，模式，运动FROM @gps'

表名不应该是数字

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

解决方案

@gps is a variable: this does not include the value in the variable, it includes the name of it:

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:

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

query is not well formatted. try this.

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'

Table Name should not be numeric