Sending "CREATE PROCEDURE" via execSql to SQL Server 2005.

[MelvinSE]

I was testing different SQL queries and noticed an issue when sending a CREATE PROCEDURE statement. I set up a simple request first:

var req = new tedious.Request(options.query, function(err, rowCount) {
  fn(err);
});

Note: fn() is my callback
"options.query" is set to a simple string:

"CREATE PROCEDURE spr_TempTemp @customerID INT AS SELECT * FROM Customers WHERE CustomerID = @customerId"

The request is then executed via execSql():

conn.execSql(req);

The query is send to the server, but an SQL error is returned:

Incorrect syntax near the keyword 'PROCEDURE'

From the error, it sure seems as if it's an issue with my query. However, running the same query in SQL Management Studio using sp_executesql is successful. Please note: I had to use a Unicode designator (N') in front of the string for it to work in Management Studio, but I believe Tedious sends the query in this way.

There are a number of rules in SQL relating to CREATE and ALTER, such as when they can be executed (only as the first statement in a batch) and how the server replies to them. Where these rules affect the Tedious requests is where I'm unsure.

I also have NOT tried to use the execute() method to run the query since this requires preparation of the request on the server first (and then clean up via unprepare() later). I will test using execute(), but needing to use this method would mean a decent amount of refactoring. Considering the current implementation of execSql() works so well for every other query I throw at it, I was hoping to avoid this.

Have you tried to run a CREATE (or ALTER) PROCEDURE via execSql?

Thanks.

[pekim]

execSqlBatch might be what you need for this. From memory, it doesn't use any procedure to execute the statement(s).

[patchusen]

I have verified that execSqlBatch works for "ALTER PROCEDURE", and would probably work for CREATE as well. Thanks for this project, btw. Saved me from ODBC hell on Node.js.

[patriksimek]

Closing due to inactivity.