如何使用SQL Server将表名作为参数传递给SP [英] how to pass a table name as parameter in a SP using SQL Server
问题描述
请给出以下测试SP的解决方案:
创建程序TestSP(@ExistTable nvarchar(128),@ NewTable nvarchar(128))
as
开始
从@ExistTable选择*进入@NewTable
结束
错误显示
Msg 102,Level 15,State 1,Procedure TestSP,Line 5
'@NewTable'附近的语法不正确。
如果我使用
创建程序TestSP(@ExistTable nvarchar(128),@ NewTable nvarchar(128))
as
开始
从[@ExistTable]中选择*进入[@NewTable]
end
然后SP创建但不执行,错误显示:
exec TestSP('test2','test1' )
Msg 102,Level 15,State 1,Line 1
'test2'附近的语法不正确。
请帮帮我.....
please give solution of the test SP below :
create procedure TestSP(@ExistTable nvarchar(128),@NewTable nvarchar(128))
as
begin
select * into @NewTable from @ExistTable
end
Error shows
"Msg 102, Level 15, State 1, Procedure TestSP, Line 5
Incorrect syntax near '@NewTable'."
if I use
create procedure TestSP(@ExistTable nvarchar(128),@NewTable nvarchar(128))
as
begin
select * into [@NewTable] from [@ExistTable]
end
Then SP creates but not execute and the error shows:
exec TestSP ('test2','test1')
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'test2'."
Please help me out.....
推荐答案
在大多数SQL查询中你不能使用变量(最常见的原因是年龄)...
你需要做的是创建一个动态SQL ...
这样的事情:
In most SQL queries you can not use variables (the most common reason is age)...
What you have to do is create a dynamic SQL...
Something like this:
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = "select * into " + @NewTable + " from " + @ExistTable
EXEC(@SQL)
https://msdn.microsoft.com/en-us/library/ms188001.aspx [ ^ ]
这篇关于如何使用SQL Server将表名作为参数传递给SP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!