如何插入带有表名的记录作为输入参数 [英] How to insert record with table name as input parameter
问题描述
我在sql中有一个表,其设计是:
I have a table in sql whose design is :
Name varchar(100) ,
DateAndTime datetime,
Price int
Discount int
我想写一个存储过程,其中表名作为输入给出
并通过该查询记录插入。
i尝试过很多但却失败了:(
I want to write a stored procedure in which table name is given as an input
and through that query record get inserted.
i have tried many things but failed :(
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = N'insert into'+
QUOTENAME(@tableName)+
+ 'values' + '(' + @a + ',' + CONVERT(varchar(20), @b,101) + ',' + convert(varchar(100), @c,101) +',' +convert(varchar(100), @d,101) +')'
EXECUTE sp_executesql @Sql
请帮助!!!!
Please help !!!!
推荐答案
你可以使用参数sp_executesql
you can use parameters withsp_executesql
SET @Sql = N'insert into'+
QUOTENAME(@tableName)+
+ 'values(@a,@b,@c,@d)'
EXECUTE sp_executesql @Sql,
N'@a nvarchar(100), @b datetime, @c int,@d INT',
@a,@b,@c,@d
检查此示例SQL小提琴 [ ^ ]
很难用不同类型的数据值构建sql语句,特别是日期时间类型。使用参数并让数据库服务器完成其余的工作:)
您需要了解更多关于 sp_executesql
的信息?然后关注这些资源
构建动态SQL In存储过程 [ ^ ]
http://technet.microsoft .com / zh-CN / library / ms188001.aspx [ ^ ]
Check this sample SQL Fiddle[^]
it is difficult to build sql statement with different types of data values, specially datetime types. use parameters and let the database server to do the rest :)
You need to learn more about sp_executesql
? then follow these resources
Building Dynamic SQL In a Stored Procedure[^]
http://technet.microsoft.com/en-us/library/ms188001.aspx[^]
这篇关于如何插入带有表名的记录作为输入参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!