CODEWITHSUNDEEP
c#sql-serverstored-procedures
coding
coding

Reputation: 63

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

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";
}

Upvotes: 0

Views: 212

Answers (1)

Thom A
Thom A

Reputation: 95554

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.

Upvotes: 3

Related Questions