如何在插入SQL语句中传递带有参数的列名 [英] how to pass column name with parameter in insert sql statment

问题描述

如何在插入sql语句中传递带有参数的列名 例如

@name='name'
insert into employees (id,@name) values(1,'a')

这可能吗?

解决方案

不，您不可能需要在应用程序中或通过在SQL Server本身中使用动态SQL来构建包含该列的SQL语句.

RE:什么是动态sql".方法如下.

DECLARE @Value nvarchar(100)
DECLARE @InsertString nvarchar(1000)
DECLARE @name sysname

SET @name ='name'
SET @Value = 'a'

SET @InsertString= 'INSERT INTO EMPLOYEES (id,' + @name + ') values(1, @Value)'


EXEC sp_executesql @InsertString, N'@Value nvarchar(100)', @Value 

但是，它有2个问题. @name必须是SQL注入证明.出于相同的原因，您还希望为@Value使用参数.但是，必须事先指定该数据类型，这意味着它不适用于所有列.因此，在反射时，最好在应用程序层中执行此操作. (注意:关于SQL注入的相同考虑仍然适用，并且您的应用程序代码将需要为列添加正确类型的参数)

how to pass column name with parameter in insert sql statment e.g.

@name='name'
insert into employees (id,@name) values(1,'a')

is this possible?

解决方案

No it isn't possible you would need to build the SQL Statement including the column either in your application or by using dynamic SQL in SQL Server itself.

Edit: RE: "what's dynamic sql". The approach is as follows.

DECLARE @Value nvarchar(100)
DECLARE @InsertString nvarchar(1000)
DECLARE @name sysname

SET @name ='name'
SET @Value = 'a'

SET @InsertString= 'INSERT INTO EMPLOYEES (id,' + @name + ') values(1, @Value)'


EXEC sp_executesql @InsertString, N'@Value nvarchar(100)', @Value 

However there are 2 issues with it. First @name must be SQL Injection proof. For the same reason you would also want to use a parameter for @Value. However the data type for that must be specified in advance meaning that it won't be suitable for all columns. So on reflection you would be better off doing this in your application layer. (NB: The same considerations about SQL injection still apply and your application code will need to add the parameter of the correct type for the column)

这篇关于如何在插入SQL语句中传递带有参数的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！