I have a number of stored procedures in my database that aren't supposed to return a result set. However, if I don't end each one with SELECT 1, the stored procedure does not show up in the list of stored procedures that you can create an EF function import for.

Why? Is it because of the word "function" in the name? Really, all I want to do is call a stored procedure from the Entity Framework model.

Most of the stored procedures execute a single INSERT INTO ON EXISTING UPDATE statement. I don't need the procedure to return anything, I just want to call it. I can leave the SELECT 1 statements in the procedures, it's not hurting anything, but it'd be nice not to have it.

Can anyone shed some light on this for me?

EDIT:

I've been busy getting new releases out the door. Today, I had to modify a stored procedure and update the model and I ran into this again. Let me provide some additional information.

I save the stored procedure in the database and it is added without any errors. I go into Visual Studio 2012. I open up my Entity Framework 4 model for the database. I right-click on the model's surface and choose "Update Model from Database ...". If the "SELECT 1" line is removed or commented out from the stored procedure, it does not show up on the "Add" tab on the Update Wizard. It only shows up on the "Add" tab on the Update Wizard if it returns a result set.

This is the problem I'm talking about. My code is a procedure. It doesn't need to return anything and my code doesn't expect it to. But I can't add the stored procedure to the model unless it returns something. So I have a number of stored procedures in my database, all ending in "SELECT 1", just so they'll show up in the Add tab of the Update Wizard and I can create function imports for them.

Why? Is it Entity Framework that's at fault? Is it the SQL Anywhere ADO .NET driver? Can you call a stored procedure that doesn't return anything using Entity Framework? If you can, why can't I import such a stored procedure into my model?

We are running 12.0.1.3895. I have a 67 KB text file with the result from turning on request-level logging. How do I get that to you?

Also, the EF model was created with EF 4. We're using the EF 5 DLLs for .NET Framework 4 in our application (I think they're EF 4.5). I don't think it makes a difference but thought I'd include it in case it does.

If you would like us to look at the log with you, you should open a technical support case underneath your support ID.

If you'd like to continue trying to debug this issue over the forum, you'll want to look for the actual query that is sent when the EF4 importer connects. Searching for some of the query terms that I posted from my query may identify the query faster for you in the request-level log.

Update #1:CR #747308 has now been resolved in 12.0.1.3978. The Windows x86/x64 EBF has been requested.

EDIT 2: We just finished some testing and we can verify that the IF NOT EXISTS followed immediately by the ALTER PROCEDURE command, when run in dbisql.exe, causes the comment to appear in front of the CREATE PROCEDURE in the proc_defn column.

Thank you for the addition - I can now reproduce this aspect easily too. I have submitted the procedure comment issue for engineering review to see if this is the intended behaviour (CR #747519) and have also submitted the ADO.NET EF query issue to be fixed (CR #747308).

I did a little spelunking in my database. I have a stored procedure in my database which I haven't been able to create a function import for. The only way I was able to create one was to edit it and add a "SELECT 1" to the end of the stored procedure.

It turns out that the problem is not that the stored procedure was missing a SELECT 1. If you look at the second line of the WHERE clause in the query that Jeff posted, it's expecting the procedure definition to start with either 'CREATE PROC' or 'ALTER PROC'. In the very first character. The script that we wrote to propagate changes to our database schema to existing installs includes a comment:

/* Version 38 */

The proc_defn column for this stored procedure starts with this comment and the CREATE PROCEDURE statement is the very next thing after it.

I don't know why or how that comment made it into the proc_defn column in the system table, but it did. And if the query that Jeff included is the query that is indeed used by Visual Studio when it queries the database for the list of stored procedures, then this comment is the problem. Either the query needs to be modified so it ignores comments at the start of the procedure definition, like the parser does, or I don't know what.

To be thorough, I edited the stored procedure in question in Sybase Central. All I did was open the stored procedure & hit Ctrl-S to save it. Sure enough, the comment got moved after the CREATE PROCEDURE (after the left parentheses but before the first parameter). Jeff's query now sees this stored procedure.

And, when I run the Update Model from Database command in Visual Studio, sure enough, the stored procedure shows up in the list. I can create a function import and everything works. There is no SELECT 1 at the end of the stored procedure. Which I guess means I can go and delete those lines from the other stored procedures, except I'm probably not going to, yet. There are other fish to fry.

EDIT:

We break our changes up into versions. As we fix issues or implement new functionality, each version is a set of changes being made to the database in a block. There's a table in our database called "CarSystem_Versions". There's a row in that table for each version that has been applied to the database.

The script began as an export of the base database schema, followed by a series of blocks of statements, one for each version change.

Start with something like this and see if that does it. If it doesn't, I don't know how it happens.

I'm no longer sure about dbisqlc.exe. I know for a fact that this issue happens when the script is loaded into dbisql.exe and executed. And, just to make sure you've got everything you need, here's the DDL for the CarSystem_Versions table:

EDIT 2:
We just finished some testing and we can verify that the IF NOT EXISTS followed immediately by the ALTER PROCEDURE command, when run in dbisql.exe, causes the comment to appear in front of the CREATE PROCEDURE in the proc_defn column. This happens even if the stored procedure's body is empty. If there's any other statement between the two, no comment goes into the column. Which is actually preferable, if you ask me.

To add some more information, our product's installer uses dbisqlc.exe to execute our update script. When I create a new database on my workstation, I use the 64-bit version of Interactive SQL (dbisql.exe) to run the script.

Thank Tony for coming back here and posting the results of your investigation. I am going to investigate if we can improve this situation once the database gets into this state.

I don't know why or how that comment made it into the proc_defn column in the system table, but it did.

I think this is the key issue here that we're not expecting - do you have any simple steps to reproduce just this artifact? (Can you run a SQL script from dbisqlc and see it show up like this in the system tables?)

If there's any other statement between the two, no comment goes into the column. Which is actually preferable, if you ask me.

In my experience, comments in front of CREATE PROC/ALTER PROC seem always to make it into the proc_defn column, however just behind the words "CREATE PROC " - i.e. immediately before the procedure's name. (And interestingly enough, it's not part of the "source" column.)

I've always felt that this is not necessarily expected behaviour but a "nice" feature to conserve my comments...

(Note: I've not faced the particular situation with IF NOT EXISTS / ALTER ...)