Hi,
Is it possible to create a table in a stored procedure, where the table name
comes as a string procedure parameter?
Sorry, I am a newbie, maybe it is not possible this way,
but then what is the suggested way?
this results error in SQL Management Studio, if I press Parse.
>Incorrect syntax near '@.tableName'.
the "CREATE TABLE MyFixNameTable" line works, but it fixes the table name.
Code Snippet
CREATE PROCEDURE CreateMyTable
-- Add the parameters for the stored procedure here
@.tableName nvarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- CREATE TABLE MyFixNameTable
CREATE TABLE @.tableName
(
"^First Name" varchar(25) NOT NULL,
"^Last Name" varchar(25) NOT NULL
)
END
You can't supply an object name as a variable/parameter to a SQL statement.
However, you could create the entire SQL statement as a string, and then use sp_executesql to execute that string.
You may find this article useful:
|||
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Like Arnie saie, you cannot create a table like this. Generally speaking, it is rarely a good thing to be programatically creating permanent tables to start with. You can do this with dynamic sql, but why? If you are going to load the data with the results of a query, it is likely best for you to do something like:
select firstName, lastName
into yourTableName
from ...
It is usually faster and avoids some logging overhead. The best way to do this is usually to have a permanent table that includes some other column to denote when you searched for data, etc, some discriminator. Then you can work with the data in the same tables every time you do this, and you code is simplified, and the data is available more readily for reporting what is being done.
