StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIncorrect SQL syntax error ADO.NET
text
Body
copied!<p>When I try to run this i get an SqlException with the message "Incorrect syntax near the keyword 'PROCEDURE'."</p> <pre><code>120: cmd.CommandText = @"CREATE PROCEDURE FindState @Name nvarchar(50) AS SELECT IDState FROM State WHERE Name=@Name"; 121: cmd.ExecuteNonQuery(); </code></pre> <p>Stack trace:</p> <pre><code>at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at xxx.Program.Main(String[] args) in C:\xxx\Program.cs:line 121 </code></pre> <p>However I tried copying the SQL command directly in a SQL query and executing it and it worked. (it created the procedure)</p> <p>To give you some context here is a little bigger section of the code</p> <pre><code>SqlParameter stateName = new SqlParameter(); stateName.SqlDbType = SqlDbType.NVarChar; stateName.Direction = ParameterDirection.Input; stateName.ParameterName = "@Name"; stateName.Size = 50; Console.Write("Enter state name: "); stateName.Value = Console.ReadLine(); cmd.Parameters.Add(stateName); cmd.CommandText = @"IF EXISTS (SELECT name FROM sysobjects WHERE name='FindState' AND type='P') DROP PROCEDURE FindState"; cmd.ExecuteNonQuery(); cmd.CommandText = @"CREATE PROCEDURE FindState @Name nvarchar(50) AS SELECT IDState FROM State WHERE Name=@Name"; cmd.ExecuteNonQuery(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "FindState"; sdr = cmd.ExecuteReader(); if (sdr.HasRows) { while (sdr.Read()) { Console.WriteLine("ID state " + stateName.Value + " is " + sdr[0]); } } else { Console.WriteLine("No such state exists"); } sdr.Close(); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload