EF reading return value Stored Proce RETURN(X)

Question

Some stored procedures do some checks and when those fails, return a specific value. If nothing fails, some return a SELECT statement, some return NOTHING (like the sample below).

Note: Stored Procedures canNOT be changed at this point and the sample below is just a sample to show the RETURN and the SELECT differences.

/* THIS IS A SAMPLE PROC */

CREATE PROCEDURE [dbo].[Register]
(
   @Email varchar(200)
)
AS 
BEGIN

    SET NOCOUNT ON ;

    -- Check if user table has an Active record with this email
    IF EXISTS(SELECT * FROM Users WHERE Email = @Email AND Activated = 0)
    RETURN(1)

    -- Check if user table has an Active record with this email
    IF EXISTS(SELECT * FROM Users WHERE Email = @Email AND Activated = 1)
    RETURN(2)


    -- Create New User record           
    INSERT  INTO Users (Email, xxx)
    VALUES  (@Email, xxxx)

END

Using EF5 and importing the stored procedures into the EDMX, how can I get the value 1 or 2 in case of problems or nothing in case the stored procedures went thru successfully?

-- 0, 1 or NULL returned SELECT @activated = Activated FROM Users WHERE Email = @Email; IF (@activated IS NOT NULL) BEGIN RETURN (@activated +1); END — billinkc, Oct 09 '13 at 18:17
The stored procedure is just a sample ...the problem is not how to calculate the value but how to read back the value from the EF5 model. — SF Developer, Oct 09 '13 at 18:21
I understand, which is why I went the comment route. Simply wanted to point out that you can reduce the time spent in the proc querying for the same data which in turn can reduce opportunity for locking & blocking — billinkc, Oct 09 '13 at 18:23

score 0 · Answer 1 · answered Oct 09 '13 at 18:15

0

USe out parameter in store procedure.

answered Oct 09 '13 at 18:15

Sonu

458
5
13

Bassam Alugili · Answer 2 · 2013-10-09T18:22:37.223

0

   var result = dbContext.Database.SqlQuery<string>("QUERY TEXT OR PROUCEDURE TEXT").FirstOrDefualt();

Check if the result not null than it must be 1 or 2.

you can also call the stored procedure as following:

var firstName = "test";
var id = 12;
var sql = @"Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);

But ExecuteSqlCommand does not return a result!

edited Oct 09 '13 at 18:22

answered Oct 09 '13 at 18:16

Bassam Alugili

16,345
7
52
70

I'm not planning to use EXECUTESQLCOMMAND passing the Stored Procedure as a string. I'm using EF5 to import the existing Stored Procedures. – SF Developer Oct 09 '13 at 18:22
Than why you just using Function Imports and Output Parameters? see this http://msdn.microsoft.com/en-us/data/gg699321(v=msdn.10).aspx – Bassam Alugili Oct 10 '13 at 08:03

score 0 · Answer 3 · answered Oct 09 '13 at 18:26

0

By default the SPROC will return 0 if no other value is given and no error is thrown. I don't know if that works in all of your scenarios (maybe you are explicitly returning zero in one of them) but in this one, you will still get the 0 response when you aren't explicitly returning anything.

answered Oct 09 '13 at 18:26

randcd

2,263
1
19
21

If a return is set it's surely NOT "0" so we are good there. My question is how do I read that return value is the expected return is a SELECT field1, field2, field3 statement (if no problems are found on the stored proc)? – SF Developer Oct 09 '13 at 18:30
Ok, what you are describing is different from a `RETURN` statement. You cannot RETURN a result set. You can select one. The `RETURN` value must be an int. If you are `RETURN`ing a result set you are using a `FUNCTION` not a `PROCEDURE`. See this [answer](http://stackoverflow.com/a/14735479/1633991) for a way to get the return Value along with the result set without having to rewrite your procedures. the UPPERCASE isn't yelling, btw, just conditioned to writing those keywords in caps. – randcd Oct 09 '13 at 18:50

EF reading return value Stored Proce RETURN(X)

3 Answers3