SQL Server Performance Forum – Threads Archive

SP parameter as tablename issues

Group, I’m trying to create a copy of a table using the SELECT INTO SQL statement in one of my store procedures, but I keep getting the error: Incorrect syntax near: ‘@questions_table’. My stored procedure looks like this: CREATE PROCEDURE CreateUserMultipleTable
@questions_table varchar (50)
AS SELECT tblMultipleChoice.* INTO @questions_table FROM tblMultipleChoice
GO Now, if I was to change @questions_table to a static value like "tblSomething" everything works fine. Any ideas or workarounds? James E Mace Flying Ace Software Solutions
http://www.jimmace.com

---

When you use a variable in place of an object name, SQL thinks youre trying to insert into the variable. It wont resolve the variable to a string, and then automatically construct an sql command out of that resolved string So you must do it manually declare @s varchar(4000)
select @s = ‘SELECT tblMultipleChoice.* INTO ‘ + @questions_table + ‘ FROM tblMultipleChoice’
exec sp_executesql @s

---

As usual, here’s an interesting link for dynamic sql. Read this and decide if that’s your way to go
http://www.sommarskog.se/dynamic_sql.html ———————–
–Frank
http://www.insidesql.de
———————–

---

]]>