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

Answer 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);