Why is my output parameter in stored procedure called from Entity Null?

Question

I am calling a stored procedure and declare an output paramters:

CREATE PROCEDURE dbo.usp_getIsReadyForProcess
@VideoId INT ,
@val INT OUTPUT
AS
BEGIN
BEGIN TRY
    BEGIN TRANSACTION
-- LOCK ROW UNTIL END OF TRANSACTION
        SELECT * FROM dbo.ProcessStatus WITH (ROWLOCK, HOLDLOCK) WHERE VideoId = @VideoId

And then setting the value throughout the transaction

  --If there is no row count
IF @@ROWCOUNT = 0
BEGIN
    SET @val = 0
END

-- If video is already in process
ELSE IF @statusCode > 1
BEGIN
    SET @val = 0
END

...... more if blocks

          -- RELEASE LOCK
    COMMIT TRANSACTION
END TRY

Here is the c# code for getting the output parameter:

        using (var db = EntityFactory.GetInstance())
        {
            ObjectParameter objParam = new ObjectParameter("val", typeof(int));
            db.usp_getIsReadyForProcess(videoId, objParam);

            return (int)objParam.Value == 1;
        }

... But then objParam.Value is null no matter what I do.

So I dig a little deeper and uncover an exception that was handled already,

Message: Method may only be called on a Type for which Type.IsGenericParameter is true.

What am i doing wrong? I tried type(bool, int32, string) .. nothing works

Have you looked at this post: stackoverflow.com/questions/5881359/… — David Tansey
– David Tansey, Commented Feb 22, 2015 at 23:00
@DavidTansey Hmm, good link. So you need to ensure the procedure has executed before checking the return value. — DavidG
– DavidG, Commented Feb 22, 2015 at 23:03

EkoostikMartin · Accepted Answer · 2015-02-22 23:01:46Z

1

Your stored procedure returns a resultset, due to this line:

SELECT * FROM dbo.ProcessStatus WITH (ROWLOCK, HOLDLOCK) WHERE VideoId = @VideoId

You can either change the SQL to something like:

DECLARE @RowCount INT
SELECT @RowCount = COUNT(*) FROM dbo.ProcessStatus WITH (ROWLOCK, HOLDLOCK) WHERE VideoId = @VideoId

Or you can capture the resultset in your code:

var resultSet = db.usp_getIsReadyForProcess(videoId, objParam);

answered Feb 22, 2015 at 23:01

EkoostikMartin

6,9313 gold badges35 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is my output parameter in stored procedure called from Entity Null?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related