?
Solved

Create SQL 2008 R2 Table using a passed table name parameter

Posted on 2013-12-15
4
Medium Priority
?
385 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 332 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 332 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 336 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 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