How do I insert SQL values into an SQL Command to create a table?-CodePudding

I'm trying to create a table in SQL from Visual Studio and I keep getting an exception that reads "'Incorrect Syntax Near '@tableName'.'" Am I using the parameter values incorrectly, I've tried using just Parameters.Add("@tableName", SQLDbType.VARCHAR) and adding the value manually, but I get the same exception.

public string CreateTable(string tableName)
           try
           {
               using (SqlConnection conn = new SqlConnection(this.connectionstring))
               {
                   SqlCommand cmd = new SqlCommand($"CREATE TABLE @tableName(indicatorid INT IDENTITY(1,1) NOT NULL PRIMARY KEY, value REAL, date DATE, ticker VARCHAR(20))", conn);
                   cmd.Parameters.AddWithValue("@tableName", tableName);
                   conn.Open();
                   var result = cmd.ExecuteNonQuery();
                   conn.Close();
                   return "Database Initialized";
               }
           }
           catch
           {
               return"Failure Creating Test Table";
           }

CodePudding user response：

You need to use dynamic SQL for this.

Be careful: you must correctly escape the table name using QUOTENAME
I would advise you not to blindly catch all exceptions. Let them pass through to the calling function, which can decide how to display or log the error.
The parameter should be nvarchar(128)
Note the use of a verbatim multi-line string using @""

public void CreateTable(string tableName)
{
    const string query = @"
DECLARE @sql nvarchar(max) = '
CREATE TABLE '   QUOTENAME(@tableName)   '(
  indicatorid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  value REAL,
  date DATE,
  ticker VARCHAR(20)
)
';

EXEC sp_executesql @sql;
";
    using (SqlConnection conn = new SqlConnection(this.connectionstring))
    using (SqlCommand cmd = new SqlCommand(query, conn)
    {
        cmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

CodePudding user response：

You can use Stored procedure with parameter like

using (SqlCommand cmd = new SqlCommand("sp_Create_New_Table", con)) {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@tableName", tableName);
      conn.Open();
      var result = cmd.ExecuteNonQuery();
      conn.Close();
      return "Database Initialized";
    }