Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How create table with stored procedure in SQL Server

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

like image 421
Mohammad Hossein Avatar asked Oct 11 '25 08:10

Mohammad Hossein


1 Answers

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
like image 115
Jayasurya Satheesh Avatar answered Oct 16 '25 09:10

Jayasurya Satheesh



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!