Error calling SQL Server stored procedure from C# code

Question

I have a stored procedure to create table in a SQL Server database. I need to call this stored procedure from C#. I get an exception

Incorrect Syntax near @TABLENAME

How do I fix this? I have the table name and column list from a xml file.

Stored procedure:

CREATE PROCEDURE PROC_CREATE_SFCOM_TABLE2
    @TABLENAME  VARCHAR(4000) ,
    @COLUMNLIST VARCHAR(4000) ,
    @ERRORMSG   VARCHAR(4000) OUTPUT 
AS 
BEGIN
    SET NOCOUNT ON   
        DECLARE @EXEC_IMMEDIATE_VAR VARCHAR (4000)
        SELECT @EXEC_IMMEDIATE_VAR  = 'CREATE TABLE @TABLENAME@COLUMNLIST ' 
        EXECUTE (@EXEC_IMMEDIATE_VAR)
END
GO

C# Code:

using (SqlConnection conn = new SqlConnection(connstring)
{
    using (SqlCommand cmd = new SqlCommand("dbo.PROC_CREATE_SFCOM_TABLE2",conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        createScript = "("+columnScript+")";

        if (tableTableSpace != null)
        {
           if (tableTableSpace != "" || tableTableSpace != string.Empty)
           {
               createScript += "TABLESPACE " + tableTableSpace;
           }
        }

        SqlParameter parameter = new SqlParameter("@TableName",SqlDbType.NVarChar);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = tableName;
        cmd.Parameters.Add(parameter);

        SqlParameter parameter2 = new SqlParameter("@COLUMNLIST",SqlDbType.NVarChar);                        
        parameter2.Direction = ParameterDirection.Input;
        parameter2.Value = createScript;
        cmd.Parameters.Add(parameter2);

        SqlParameter parameter3 = new SqlParameter("@ErrorMsg", SqlDbType.NVarChar);
        parameter3.Direction = ParameterDirection.Output;
        parameter3.Size = 4000;
        cmd.Parameters.Add(parameter3);

        conn.Open();
        cmd.ExecuteNonQuery();
        string errorMsg = parameter3.Value.ToString();

        if (errorMsg != string.Empty)
           LogInfo("Error: " + errorMsg);

Are you getting the error when you run your C# code or when you execute the SQL to create the stored procedure? — n8wrl
– n8wrl, Commented Nov 7, 2014 at 15:17

Soner Gönül · Accepted Answer · 2014-11-07 14:50:33Z

3

You can't parameterize your table or column names. You can only parameterize your values. Only way is to execute dynamic sql.

But you should have a very strong validation for that if you want to create a table dynamic. Create a white list for that for example.

Before you do that, read: The Curse and Blessings of Dynamic SQL

edited Nov 7, 2014 at 14:50

answered Nov 7, 2014 at 14:42

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

Comments

James · Accepted Answer · 2014-11-07 15:09:22Z

2

You didn't create the dynamic SQL string correctly. Change it to this...

CREATE PROCEDURE PROC_CREATE_SFCOM_TABLE2
    @TABLENAME  VARCHAR(4000) ,
    @COLUMNLIST VARCHAR(4000) ,
    @ERRORMSG   VARCHAR(4000) OUTPUT 
AS 
BEGIN
    SET NOCOUNT ON   
        DECLARE @EXEC_IMMEDIATE_VAR VARCHAR (4000)
        SELECT @EXEC_IMMEDIATE_VAR  = 'CREATE TABLE [' + @TABLENAME + '] ' + @COLUMNLIST
        EXECUTE (@EXEC_IMMEDIATE_VAR)
END
GO

However, please note that this is a very dangerous thing to do. @COLUMNLIST could contain anything, including other SQL commands, which could be used for a SQL injection attack

answered Nov 7, 2014 at 15:09

James

4,0081 gold badge30 silver badges45 bronze badges

Collectives™ on Stack Overflow

Error calling SQL Server stored procedure from C# code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related