SQL query with c# stringbuilder error near "=" symbol

Question

I'm working on some code that creates procedures with C#. When I try to run my code:

StringBuilder sbSP1 = new StringBuilder();

sbSP1.AppendLine("CREATE PROCEDURE " + ProcN + "DELETE @id int=0 AS BEGIN DELETE FROM " + tname + " WHERE " + PK + " = @id END");

SqlConnection connection = new SqlConnection(connectionString);

using (SqlCommand cmd = new SqlCommand(sbSP1.ToString(), connection))
{
    connection.Open();
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    connection.Close();
}

This part doesn't work. It throws an exception:

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

But when I try to run this part:

StringBuilder sbSP3 = new StringBuilder();
sbSP3.AppendLine("CREATE PROCEDURE " + ProcN + "GET AS BEGIN SET NOCOUNT ON; SELECT " + columns + " from " + tname + "  END");

SqlConnection connection = new SqlConnection(connectionString);

using (SqlCommand cmd = new SqlCommand(sbSP3.ToString(), connection))
{
    connection.Open();
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    connection.Close();
}

it runs correctly.

Where is my mistake in the first snippet of code?

I'd recommend looking at the resultant query text. The problem ought to be quite obvious then. — ProgrammingLlama
– ProgrammingLlama, Commented Jul 4, 2018 at 13:04
@Afonso... uh... even for queries to create stored procedures? I don't think that would work very well. — ProgrammingLlama
– ProgrammingLlama, Commented Jul 4, 2018 at 13:05
@Afonso SQL Server doesn't support anything like that. You can't do CREATE PROCEDURE @ProcName AS ..., you'll just get a syntax error. — user743382
– user743382, Commented Jul 4, 2018 at 13:10
I'd really recommend doing as @mjwills suggested and including the exact value of sbSP1.ToString() at the time you assign it to the SqlCommand, but my initial hunch was that ProcN or PK contained a space, or some invalid character. — ProgrammingLlama
– ProgrammingLlama, Commented Jul 4, 2018 at 13:15

Dmitrii Bychenko · Accepted Answer · 2018-07-04 13:34:41Z

4

Without sbSP1 final text it's difficult to pin down the error; as a working hypothesis I suggest that PK being complex (e.g. "id value") should be escaped - [PK]. In order to avoid such kind of errors, please, never build the query, but make procedure's text being readable with a help of string interpolation - $ and verbatim strings - @.

 //TODO: check the text, is it the procedure you want to create?
 string text = 
   $@"create procedure [{ProcN}Delete] 
        @id int = 0 as 
      begin
        delete
          from [{tname}]
         where [{PK}] = @id 
      end;"; 

 //DONE: do not close connection manually, but wrap it into using
 using (SqlConnection connection = new SqlConnection(connectionString)) {
   connection.Open();

   using (qlCommand cmd = new SqlCommand(text, connection)) {
     cmd.ExecuteNonQuery();
   }  
 }

edited Jul 4, 2018 at 13:34

answered Jul 4, 2018 at 13:22

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

Collectives™ on Stack Overflow

SQL query with c# stringbuilder error near "=" symbol

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related