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:

Example

You need dynamic SQL: sommarskog.se/dynamic_sql.html#Cre_tbl Anyway table per restaurant is not best idea. — Lukasz Szozda
– Lukasz Szozda, Commented Jan 16, 2018 at 16:12
That image is implying you want your fields to start with the character @. I doubt that is your intent. What are you actually trying to do here? Also, address has 2 d's and 1 r. ;) — Thom A
– Thom A ♦, Commented Jan 16, 2018 at 16:12
I want to create restaurant program that a user has table with all foods that he buys and this table create when a user sign up in program — Mohammad Hossein
– Mohammad Hossein, Commented Jan 16, 2018 at 16:16
Never set up tables by user. The correct design is a user table with all the users and then a restaurant table with all the restaurants, then a userRestaurant table that defines the many to many relationship. If you are designing databases, you need to stop right now and read about relational design before going any further. — HLGEM
– HLGEM, Commented Jan 16, 2018 at 16:19
I couldn't possibly agree with @HLGEM more. This design is just awful. The last thing you want is a column with the name of the restaurant and another column with the name of the food. This is not well normalized and will do nothing but cause you to want to stab yourself in the long run. — Sean Lange
– Sean Lange, Commented Jan 16, 2018 at 17:31

Jayasurya Satheesh · Accepted Answer · 2018-01-16 16:27:24Z

4

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

answered Jan 16, 2018 at 16:27

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

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

3 Comments

Sean Lange Over a year ago

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.

HLGEM Over a year ago

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

HLGEM Over a year ago

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.

Collectives™ on Stack Overflow

How create table with stored procedure in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related