Solved

Create SQL 2008 R2 Table using a passed table name parameter

Posted on 2013-12-15
4
379 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
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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 been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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