How create table with stored procedure in SQL Server

Question

I want to create a stored procedure on SQL Server.

My code is:

CREATE PROCEDURE [dbo].[Pcreate]
    @name NVARCHAR(50),
    @namefood NVARCHAR(50),
    @restaurantname NVARCHAR(50),
    @pricefood FLOAT,
    @address NVARCHAR(50)
AS
BEGIN   
    CREATE TABLE [dbo].[@name]
    (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
        [@namefood] NVARCHAR(50) NOT NULL, 
        [@restaurantname] NVARCHAR(50) NOT NULL, 
        [@pricefood] FLOAT NOT NULL, 
        [@address] NVARCHAR(50) NOT NULL
    )
END

In C# code:

db.Pcreate(name.Text, restaurantbox.SelectedItem.ToString(), getEdit1(), float.Parse(getEdit2()), adrresstxt.Text);

but the code creates the table with @name like in this screenshot:

Solution

Create a Dynamic SQL Using Query String and Execute.

CREATE PROCEDURE [dbo].[Pcreate]
    @name NVARCHAR(50),
    @namefood NVARCHAR(50),
    @restaurantname NVARCHAR(50),
    @pricefood NVARCHAR(50),--Changed from Float Since It's the Column Name
    @address NVARCHAR(50)
AS
BEGIN   
    DECLARE @Sql VARCHAR(MAX) = '
    IF OBJECT_ID(''[dbo].['+@name+']'') IS NULL
    BEGIN
        CREATE TABLE [dbo].['+@name+']
        (
            [Id] INT IDENTITY(1,1) PRIMARY KEY, 
            ['+@namefood+'] NVARCHAR(50) NOT NULL, 
            ['+@restaurantname+'] NVARCHAR(50) NOT NULL, 
            ['+@pricefood+'] FLOAT NOT NULL, 
            ['+@address+'] NVARCHAR(50) NOT NULL
        )
    END
    '

    EXEC(@Sql)

END

Comments

[Sean Lange]

While this will technically work it is perpetuating the absolutely horrific "design" the OP is using. The whole concept of this table design is mortally flawed.

[dburges]

Do not consider doing this unless you read and understand the risks in this article. It is extremely bad to use dynamic SQL unless you are an expert. sommarskog.se/dynamic_sql.html

[dburges]

Voted down for suggesting dynamic SQL to someone clearly not experienced enough to use it without danger. There are times when it is inappropriate to blindly give the answer and this is one of them.