Solved

Create SQL 2008 R2 Table using a passed table name parameter

Posted on 2013-12-15
4
381 Views
Last Modified: 2013-12-22
Hi
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.
0
Comment
Question by:Adlerm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 11

Assisted Solution

by:Otana
Otana earned 83 total points
ID: 39720944
CREATE PROCEDURE sp_test
      -- Add the parameters for the stored procedure here
      @TABLE_NAME varchar(255)
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
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>
END
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 83 total points
ID: 39721098
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

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 84 total points
ID: 39721858
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
GO
CREATE PROCEDURE sp_create_table (
    @table_name sysname,
    @columns nvarchar(max) = NULL
    )
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max)
IF @table_name LIKE '#%'
BEGIN
    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
END --IF
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
GO
EXEC sp_MS_marksystemobject 'sp_create_table'
GO
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
GO
0
 

Author Closing Comment

by:Adlerm
ID: 39735278
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.
Cheers
Michael Adler
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question