-2

Is it possible in C# to execute multiline SQL statement in one call without creating Stored Procedure with this SQL?

Here is what I am trying to do:

string mySQL = "with CTE_table as (....)" +
               "select name from CTE_table where ...." ;

_sql_Command = _sql_conn.CreateCommand();
_sql_Command.CommandText = mySQL ;
SqlDataReader dataReader = _sql_Command.ExecuteReader();

if (dataReader.Read())
{
...

I found a suggestion to separate SQL by ";", in this case I am getting a syntax error

Thanks,

zb

Robert McKee
  • 21,305
  • 1
  • 43
  • 57
Zalek Bloom
  • 551
  • 6
  • 13
  • 3
    Show your sql, you can separate statements with `;` – Crowcoder Oct 05 '18 at 20:26
  • 1
    Look at this answer https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand – Sql Surfer Oct 05 '18 at 20:37
  • When you do this, you need to fetch the results separately: https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand. You can also use Dapper, and it has a "QueryMultiple" extension method – Flydog57 Oct 05 '18 at 20:50
  • 1
    That is a single statement. `WITH` is a clause of the `SELECT` statement. The select won't work without the first, and the first without the second is also a syntax error. You most likely are missing the space between them which is causing a syntax error. – Robert McKee Oct 05 '18 at 20:50
  • Edited question to reflect the correct question. – Robert McKee Oct 05 '18 at 20:55
  • Don't forget that you wouldn't want to execute a straight SQL statement like this without parameterized `WHERE` clause. SQL Injection will make your day very long and un-fun. – Shawn Oct 05 '18 at 21:04

2 Answers2

2

Try

 var mySQL = @"with CTE_table as (....) 
             select name from CTE_table where ...." ;

sometimes I find this approach handy

   var whereclause = "....";
   var mySQL = $@"with CTE_table as (....) 
                 select name from CTE_table where {whereclause}" ;
Kirsten
  • 15,730
  • 41
  • 179
  • 318
-2

I changed my SQL to one SQL without using "WITH CTE" and now with one SQL it is working. zb

Zalek Bloom
  • 551
  • 6
  • 13
  • Kirsten Greed's answers below should both work because you are just missing a white space character, and both of her answers correctly include a white space character in them. – Robert McKee Oct 05 '18 at 20:57