Solved

Create SQL 2008 R2 Table using a passed table name parameter

Posted on 2013-12-15
4
376 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:
ScottPletcher 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now