Dynamic Name for Database Table - Stored Procedure

Question

I want to create dynamic name for my database tables. I declare variable and use it as my table name.

ERROR: Incorrect syntax near '@sample'. Expecting '.',ID or QUOTED_ID

CREATE PROCEDURE [dbo].[sp_SAMPLE]
    @name nvarchar(50)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sample nvarchar(50);
SET @sample = 'tbl_function_' + @name;

Create Table @sample
(
    id int not null,
    UserType nvarchar(50) null,
    paramater2 nvarchar(50) null
)
END

Is there any way to make my table name dynamic? Thank you.

Gordon Linoff · Accepted Answer · 2017-03-09 02:34:03Z

2

I'm not sure why you would want to do this. But, you can do it using dynamic SQL:

CREATE PROCEDURE [dbo].[sp_SAMPLE]
    @name nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max);

    SET @sql = '
Create Table tbl_function_@name
(
    id int not null,
    UserType nvarchar(50) null,
    paramater2 nvarchar(50) null
)';

    SET @sql = REPLACE(@sql, '@name', @name);

    EXEC sp_executesql @sql;
END;

Why would you want to create a separate table for each @name, instead of just inserting rows with @name in a column?

answered Mar 9, 2017 at 2:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EnzoZow Over a year ago

Thank you it works now. Also thank you for your idea adding column @name to my table.

udhaya kumar · Accepted Answer · 2017-03-09 04:17:17Z

0

CREATE PROCEDURE [dbo].[sp_SAMPLE]
@name nvarchar(50)
AS
BEGIN
SET NOCOUNT ON

DECLARE @sql varchar(4000);

SET @sql = '
Create Table tbl_function_'+@name+'
(
id int not null,
UserType nvarchar(50) null,
paramater2 nvarchar(50) null
)'

EXEC (@sql) 
END

answered Mar 9, 2017 at 4:17

udhaya kumar

1696 bronze badges

2 Comments

Ted Over a year ago

some comments would be nice, otherwise it's just a code-dump

udhaya kumar Over a year ago

@Ted this is another way to get results not a code-dump

Collectives™ on Stack Overflow

Dynamic Name for Database Table - Stored Procedure

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related