sql-server-express sql-server-2005-express

如何在insert sql语句中通过参数传递列名

发布于 2024-09-03 09:34:03 字数 135 浏览 4 评论 0原文

如何在insert sql语句中通过参数传递列名例如，

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

这可能吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小伙你站住 2024-09-10 09:34:03

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

编辑：回复：“什么是动态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

然而，它有两个问题。第一个 @name 必须是 SQL 注入证明。出于同样的原因，您还希望使用 @Value 的参数。但是，必须提前指定其数据类型，这意味着它并不适合所有列。因此，经过反思，您最好在应用程序层中执行此操作。（注意：关于 SQL 注入的相同注意事项仍然适用，您的应用程序代码将需要为列添加正确类型的参数）

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)

回复收藏 0 原文

~没有更多了~