Executing SQL Stored Procedure with Output Parameter from Entity Framework

Question

Using EF, I'm trying to execute a stored procedure that returns a single string value, i.e. the status of an SQL Agent Job.

The stored procedure is declared as

CREATE PROCEDURE [dbo].[up_GetJobStatus](@JobStatus NVARCHAR(30) OUTPUT)
AS

-- some code omitted for brevity

SELECT @JobStatus = (
SELECT  
    CASE job_state 
    WHEN 1 THEN 'Executing'
    WHEN 2 THEN 'Waiting for thread'
    WHEN 3 THEN 'Between retries'
    WHEN 4 THEN 'Idle'
    WHEN 5 THEN 'Suspended'
    WHEN 6 THEN '<unknown>'
    WHEN 7 THEN 'Performing completion actions'
END
FROM @xp_results results 
INNER JOIN msdb.dbo.sysjobs sj
ON results.job_id = sj.job_id
WHERE sj.job_id = @job_id)

RETURN

I have verified the stored procedure is working correct as I can execute it in query window and it returns

    @JobStatus
  ------------
  1|Idle

However when executing with EF, the param value is NULL

var param = new SqlParameter
{
    ParameterName = "@JobStatus",
    DbType = DbType.String,
    Size = 30,
    Direction = System.Data.ParameterDirection.Output
};

var result = this.etlContext.Database.SqlQuery<string>("EXEC dbo.up_GetJobStatus @JobStatus OUTPUT", param);

I've also tried the ExecuteSqlCommand method but that didn't work either.

Any ideas?

Have you tried passing it using: var result = this.etlContext.Database.SqlQuery("EXEC dbo.up_GetJobStatus {0}", param.Value)?; — Jason Loki Smith, Feb 27 '14 at 12:52
Have you had a look at this link (http://msdn.microsoft.com/en-us/library/vstudio/bb896334(v=vs.100).aspx) on how to do it? you will first need to import the stored procedures into your dbmx — Jason Loki Smith, Feb 27 '14 at 13:21

score 37 · Answer 1 · edited Jul 15 '21 at 18:15

Create stored procedure in database

CREATE PROCEDURE [dbo].myStoredProcName
    @inputParam1 VARCHAR(150),
    @inputParam2 VARCHAR(150),
    @myOutputParamBool BIT OUTPUT,
    @myOutputParamString VARCHAR(100) OUTPUT,
    @myOutputParamInt INT OUTPUT
AS
BEGIN
    -- sql here
END

Update entity model from database to include stored procedure as shown here

Call the stored procedure from C# code

// Type is System.Data.Entity.Core.Objects.ObjectParameter
ObjectParameter myOutputParamBool = new ObjectParameter("myOutputParamBool", typeof(bool));
ObjectParameter myOutputParamString = new ObjectParameter("myOutputParamString", typeof(string));
ObjectParameter myOutputParamInt = new ObjectParameter("myOutputParamInt", typeof(Int32));

using (var context = new SandCryptEntities())
{
    context.myStoredProcName(inputParam1, inputParam2, myOutputParamBool, myOutputParamString, myOutputParamInt);   
}

bool myBool = Convert.ToBoolean(myOutputParamBool.Value);
string myString = Convert.ToString(myOutputParamString.Value);
int myInt = Convert.ToInt32(myOutputParamInt.Value);

I do have Table Types as my params to SP, so They didn't come as param in sp if I follow your way, please suggest — Gaurav Kaushik, Dec 25 '20 at 06:51

score 4 · Answer 2 · answered Sep 18 '14 at 21:14

Here's an actual answer. Apparently there are serious issues with output parameters in entity framework when you're using DbContext/code first. This article has a good discussion and workaround: http://weblogs.asp.net/dwahlin/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters. Apparently it's supposed to be "fixed" with an update, but I haven't really seen that happen.

score 3 · Answer 3 · edited Jul 15 '21 at 18:17

Use the following code. It is working for me.

var param = new SqlParameter
{
    ParameterName = "@JobStatus",
    DbType = DbType.String,
    Size = 30,
    Direction = System.Data.ParameterDirection.Output
};

var result = this.etlContext.Database.SqlQuery<string>("EXEC dbo.up_GetJobStatus @JobStatus=@JobStatus OUTPUT", param);
string JobStatus = param.Value.ToString();

The first @JobStatus is your param ParameterName and the second @JobStatus is the PROCEDURE Parameter name.

score 2 · Answer 4 · edited Jul 15 '21 at 18:18

Just to build on vicky's answer.

var localPar = new SqlParameter("@userlogin", ad);
var Paramater2 = new SqlParameter("@Paramater2", "");
Paramater2.Direction = ParameterDirection.Output;
Paramater2.Size = 30;
Paramater2.DbType = DbType.String;

var parameters = new List<SqlParameter>();
parameters.Add(localPar);
parameters.Add(Paramater2);


var result = this.Database.SqlQuery<object>("EXEC dbo.uspSP @userlogin, @Paramater2 =@Paramater2 OUTPUT", parameters.ToArray());

var x = result.FirstOrDefault();

return Paramater2.Value.ToString();

If you have more than 1 parameter.

score 0 · Answer 5 · answered Jul 19 '23 at 13:23

Just need a slight change.

First Building Your Query-

var query=@"$Declare @return_Value nvarchar(30) @JobStatus nvarchar(30)
EXEC @return_Value=up_GetJobStatus @JobStatus=@JobStatus
SELECT @JobStatus as N'@JobStatus'";

Secondly, using SqlQueryRaw.

var result = this.etlContext.Database.SqlQueryRaw<string> 
("query").AsEnumerable().FirstOrDefault();

result should have the value you are looking for.

Executing SQL Stored Procedure with Output Parameter from Entity Framework

5 Answers5

Linked