5

I have a EF code-first model. It is working fine, but when I try to run a stored procedure with Database.ExecuteSqlCommand it always returns -1, it doesn't matter what I put in the stored procedure result is always -1

My stored procedure:

CREATE PROCEDURE [dbo].[Login_user] 
    @clientKey varchar(max)
AS
BEGIN
    SET NOCOUNT ON
    RETURN(0)
END

And I run it like:

return Database.ExecuteSqlCommand(EXEC [dbo].[Login_user] {0}", key);

Am I missing something?

By the way if I run the stored procedure from SQL Server Management Studio it returns 0

USE [lo9iMed]
GO
DECLARE @return_value int
EXEC    @return_value = [dbo].[Login_user]
    @clientKey = N'76F41F99-EA9E-4181-A6FB-579D23D3C2C0'

SELECT  'Return Value' = @return_value

Thanks in advance.

anibal

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2968933
  • 71
  • 1
  • 3
  • possible duplicate of [How to execute a stored procedure within C# program](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) –  Jan 21 '14 at 14:16

2 Answers2

5

You have the SET NOCOUNT ON directive included in your stored procedure (you probably used the SSMS template?). This means it will not count the rows affected by your commands. Remove this line and you should be good to go! :)

bughunter1999
  • 194
  • 2
  • 4
1

Because you are using SELECT, what you want is ExecuteScalar - or if that isn't exposed, SqlQuery<int>. You might get the result you are after if you change it to return @return_value; - but then: you might not.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 1
    Hi Marc thx, 4 your answer. I've tried ExecuteQuery("EXEC [dbo].[Login_user] {0}", key) and it throws The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types. – user2968933 Jan 21 '14 at 15:14
  • @user2968933 frankly, whatever this `Database` wrapper is here: it isn't helping you... – Marc Gravell Jan 21 '14 at 15:43