• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

Create SQL 2008 R2 Table using a passed table name parameter

Is it possible to pass a table name parameter to a stored procedure which in turn creates a table that has the name passed by the  parameter.

I have tried a number of things  - see code attached - but I can't seem to get the stored procedure to substitute the passed parameter table name in the Create command.

The sp actually creates a table but creates it with the name -  ' + tablename + ' -

It was suggested in various internet articles that the @Tablename       nvarchar(50) line of code should be defined as   @Tablename       sysname  (instead of the nvarchar(50) - which I tried but it still created the table with the  ' + @Tablename + '  name.

Other articles suggested that this could be achieved by using dynamic SQL but I can't see why my SP can't  substitute the name passed by the parameter.

If anyone can shed so light on this or can point out what I'm doing wrong I'd be most grateful as I spent hours on this but getting nowhere fast.
3 Solutions
      -- Add the parameters for the stored procedure here
      @TABLE_NAME varchar(255)
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
declare @SQL_STRING nvarchar(1000)

set @SQL_STRING = 'CREATE TABLE ' + @TABLE_NAME + ' (first_column int)'

exec sp_executesql @SQL_STRING
    -- Insert statements for procedure here
      --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
Carl TawnSystems and Integration DeveloperCommented:
You'll need to wrap the table name in square brackets, just in case someone passes a table name with a space in it:
set @SQL_STRING = 'CREATE TABLE [' + @TABLE_NAME + '] (first_column int)'

Open in new window

Scott PletcherSenior DBACommented:
It's definitely possible.

If you're going to do this, I'd create such a proc in the master db, so that the one proc can create tables in any db.  I'd also optionally allow the column definitions to be passed in.  Below is code to do that; I assume the person running the code is a sysadmin, or otherwise has a very high level of authority in the instance.

USE master
CREATE PROCEDURE sp_create_table (
    @table_name sysname,
    @columns nvarchar(max) = NULL
DECLARE @sql nvarchar(max)
IF @table_name LIKE '#%'
    RAISERROR('Cannot create temp tables using this proc, because they would always go out of scope and be deleted when the proc ended anyway.',
        16, 1) WITH NOWAIT
    RETURN -1
SET @sql = 'CREATE TABLE [' + @table_name + '] ' +
    CASE WHEN @columns IS NULL THEN '( column1 int )'
         ELSE CASE WHEN  LEFT(@columns, 1) <> '(' THEN '(' ELSE '' END + @columns +
                  CASE WHEN RIGHT(@columns, 1) <> ')' THEN ')' ELSE '' END END
EXEC sp_executesql @sql
EXEC sp_MS_marksystemobject 'sp_create_table'
use tempdb
exec sp_create_table 'test1'
exec msdb.dbo.sp_create_table 'test1', 'col1 int, col2 varchar(30), col3 datetime'
select * from tempdb.dbo.test1
select * from msdb.dbo.test1
drop table tempdb.dbo.test1
drop table msdb.dbo.test1
AdlermAuthor Commented:
My apologies for the late response - things are a bit hectic at this time of the year and I've been away for a couple of days.
If you don't mind I'll allocate the points to all three suggestions as they are all excellent and while I won't employ all three suggestions the have given me some ideas - especially updating the master db.
Many thanks for your help.
Have a great Christmas and New Year.
Michael Adler

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now