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
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
Can anyone shed some light on this for me?
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 just added a stored procedure called Foo to my database:
CREATE OR REPLACE STORED PROCEDURE FOO ( IN @Arg1 INT, IN @Arg2 INT ) BEGIN -- Some code that does something to a table in the database. -- . . . -- If the next line is not in the stored procedure, I cannot create a Function Import in the model for this stored procedure. SELECT 1; END;
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?
Update #1: CR #747308 has now been resolved in 188.8.131.5278. The Windows x86/x64 EBF has been requested.
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).
Using the query that Jeff included in his answer, namely:
SELECT '[' + u.user_name + ']' + '[' + p.proc_name + ']' AS [Id], '' AS [CatalogName], u.user_name AS [SchemaName], p.proc_name AS [Name] FROM sys.sysprocedure p JOIN sys.sysuserperm u ON p.creator = u.user_id WHERE ( u.user_name NOT IN ( 'SYS', 'dbo', 'rs_systabgroup', 'SA_DEBUG', 'ml_server' ) ) AND ( ( LOCATE( TRIM( p.proc_defn ), 'CREATE PROC' ) = 1 ) OR ( LOCATE( TRIM( p.proc_defn ), 'ALTER PROC' ) = 1 ) ) ORDER BY u.user_name, p.proc_name
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 "
It turns out that the problem is not that the stored procedure was missing a
/* Version 38 */
I don't know why or how that comment made it into the
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
And, when I run the
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.
Each version is coded in the following format:
/* Version x */ IF NOT EXISTS ( SELECT 1 FROM CarSystem_Versions WHERE VersionId = x ) THEN -- Version specific statements INSERT INTO CarSystem_Versions ( VersionId, VersionTitle, Description, VersionDate, LocaleId, CreatedDate, ModifyDate ) VALUES ( . . . ); /* Version x */ END IF; go
In looking over the script, there are four stored procedures that have this problem, only one of which I want to import. The blocks for these four versions are similar in that they look like this:
/* Version x */ IF NOT EXISTS ( SELECT 1 FROM CarSystem_Versions WHERE VersionId = x ) THEN ALTER PROCEDURE . . . INSERT INTO CarSystem_Versions ( VersionId, VersionTitle, Description, VersionDate, LocaleId, CreatedDate, ModifyDate ) VALUES ( . . . ); /* Version x */ END IF; go
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:
CREATE TABLE "DBA"."CarSystem_Versions" ( "VersionId" INTEGER NOT NULL, "VersionTitle" VARCHAR(500) NOT NULL, "Description" VARCHAR(8000) NULL, "VersionDate" TIMESTAMP WITH TIME ZONE NOT NULL, "LocaleId" VARCHAR(50) NOT NULL, "CreatedDate" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), "ModifyDate" TIMESTAMP WITH TIME ZONE NULL, PRIMARY KEY ( "VersionId" ASC ) ) IN "system";
We just finished some testing and we can verify that the