Creating a SQL table, passing a tableName as parameter using C# gives an error

Question

I am trying to create a stored procedure createNewTable which creates a new table.

If I execute the procedure as it is without C#, it works where I have hardcoded the name of the table which is testTable5 (the code which is commented).

What I am trying to do now, is to execute this stored procedure using C#. What I also want to do here is to pass a name for the table as a parameter. So I try to pass the name: testTable5.

But I get this error when I execute the C# code:

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'testTable5'

I wonder what I could be doing wrong in this code?

Stored procedure (commented out code is the original code that works as hardcoded)

--CREATE PROCEDURE createNewTable
--AS
--CREATE TABLE testTable5
--(
--    [DateTime]    SMALLDATETIME  NOT NULL,
--    [FeatureNbr]  SMALLINT         NOT NULL,
--    [Value]       FLOAT (53)       NULL,
--    [Bool]        BIT              NULL,
--  CONSTRAINT UC_testTable5 UNIQUE (DateTime),
--  CONSTRAINT PK_testTable5 PRIMARY KEY (FeatureNbr, DateTime)
--);


CREATE PROCEDURE createNewTable
    @tableName nvarchar(max)
AS
BEGIN
    DECLARE @FullQuery nvarchar(1000)
    SET @FullQuery = N'CREATE TABLE ' + QUOTENAME(@tableName) + ' ([DateTime] SMALLDATETIME NOT NULL, [FeatureNbr] SMALLINT NOT NULL, [Value] FLOAT (53) NULL, [Bool] BIT NULL, 
                     CONSTRAINT UC_' + QUOTENAME(@tableName) + ' UNIQUE (DateTime), 
                     CONSTRAINT PK_' + QUOTENAME(@tableName) + ' PRIMARY KEY (FeatureNbr, DateTime));'

    EXECUTE sp_executesql @FullQuery;
END

C# code (which executes this stored procedure)

void createNewTable()
{
    using (SqlConnection conn = new SqlConnection(GetConnectionString()))
    {
        SqlCommand cmd = new SqlCommand("createNewTable", conn); //1. create a command object identifying the stored procedure
        cmd.CommandType = CommandType.StoredProcedure; //2. set the command object so it knows to execute a stored procedure
        cmd.Parameters.Add(new SqlParameter("@tableName", "testTable5"));

        int i = cmd.ExecuteNonQuery();
    }
}

static private string GetConnectionString()
{
    return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\source\\repos\\TestDatabaseCreation\\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30";
}

Thom A · Accepted Answer · 2020-10-07 10:22:18Z

3

UC_' + QUOTENAME(@tableName) would create something like UC_[TableName]. Object names cannot contain special characters unless that are delimit identified (so if you really wanted a unique constraint with that name you would define it as [UC_[tablename]]]; but I strongly suggest against any such name).

You need to quote the entire value: QUOTENAME(CONCAT(N'UC_',@tablename)). That would produce the value [UC_tablename].

Also, the parameter @tableName should not be an nvarchar(MAX). An object's name cannot be longer than 128 characters, and there is a specific data type for object names, sysname, which is a synonym for nvarchar(128) NOT NULL.

answered Oct 7, 2020 at 10:22

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

7 Comments

coding Over a year ago

then I understand. I have to QUOTENAME everything there. This worked now when I did it that way. That was good to know. Then, it should be okay to declare it like this: @tableName nvarchar(128) ? (This worked)

Thom A Over a year ago

I personally suggest using sysname over nvarchar(128), as it is the specific data type designed for object names, @coding .

coding Over a year ago

A side question. I am not sure if it is okay to ask like this. But how can one return that the table was created successfully. This command: int i = cmd.ExecuteNonQuery(); if (i != 0) { MessageBox.Show(i + "Table Created"); } shows: -1Table Created. Shouldn´t it be: 1Table Created ?

Thom A Over a year ago

My C# knowledge is limited at best, @coding . If you want to understand that thenpost a new question in regards to that; as it's a completely different problem to what yuo have asked about here.

Dan Guzman Over a year ago

@coding, the proc with throw an error if not created successfully so a simple try/catch in the c# code is enough. Although you could return a value from the proc (return code, scalar resultset, or output parameter), doing so is just extra code without value, IMHO.

|

Collectives™ on Stack Overflow

Creating a SQL table, passing a tableName as parameter using C# gives an error

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related