C# to read OUTPUT value from stored procedure

Question

I've been trying to read RETURN value from my stored procedure for a while with no luck, in my previous post it was pointed out I should be using OUTPUT and someone provided some code on how I would do that as I've not used OUTPUT before.

I am now trying to get the OUTPUT value into my C# code.

Return 0 if the vote didn't already exist
Return 1 if the vote exists

The code currently throws an error:

System.Data.SqlClient.SqlException: Procedure or function 'Votes' expects parameter '@votecount', which was not supplied

I've viewed quite a lot of posts on here and google and not found my answer, so hoping someone on here can help me out to where i am going wrong

My C#

command = new SqlCommand($@"EXECUTE dbo.Votes @VotedMember = @@VotedMember,
                                              @VotedBy = @@VotedBy",  
                         StaticObjects._connection);

if (Context.Guild.Users.Where(x => x.Username.ToLower() == member.ToLower() || 
                                   x.Nickname?.ToLower() == member.ToLower()).Count() > 0)
{
    SqlParameter GOTWParam = new SqlParameter
    {
         ParameterName = "@@VotedMember",
         Value = //code here
    }

    command.Parameters.Add(GOTWParam);

    SqlParameter VotedByParam = new SqlParameter
    {
        ParameterName = "@@VotedBy",
        Value = //code here
    };
    command.Parameters.Add(VotedByParam);
    command.Parameters.Add("@votecount", SqlDbType.Int).Direction = ParameterDirection.Output;

    command.ExecuteNonQuery();

    int response = Convert.ToInt32(command.Parameters["@votecount"].Value);

    switch (response)
    {
        case 0:
           // do something

        case 1:
           // do something
    }
}

stored procedure

CREATE PROCEDURE [dbo].[Votes]
    @VotedMember BIGINT,
    @VotedBy BIGINT,
    @votecount INT OUTPUT

    AS

    BEGIN TRY
    BEGIN TRANSACTION t_Transaction

    SET @votecount = 0
    IF NOT EXISTS(SELECT 1 FROM [dbo].[GOTWVotes] 
    WHERE [VotedBy] = @VotedBy)
    BEGIN
        INSERT INTO 
        [dbo].[GOTWVotes] ([VotedMember],[VotedBy])
    VALUES
        (@VotedMember, @VotedBy)        
    END
    ELSE
    BEGIN
        SELECT @votecount = COUNT(*) FROM [dbo].[GOTWVotes] 
    WHERE [VotedBy] = @VotedBy
    END


    COMMIT TRANSACTION t_Transaction
    END TRY
    BEGIN CATCH
        SET @votecount = -1
        ROLLBACK TRANSACTION t_Transaction
    END CATCH

Instead of `ParameterDirection.Output`, try, `ParameterDirection.ReturnValue` — SS_DBA, Apr 08 '19 at 20:17

David Browne - Microsoft · Accepted Answer · 2019-04-08T21:19:05.287

1

You're missing a output parameter.

The TSQL would look something like:

command = new SqlCommand($@"EXECUTE dbo.Votes @VotedMember = @p_VotedMember,
                                              @VotedBy = @p_VotedBy,
                                              @p_votecount = @votecount output",  
                         StaticObjects._connection);

and in C#

var pVotecount = command.Parameters.Add("@p_votecount", SqlDbType.Int);
pVotecount.Direction = ParameterDirection.Output;

Where the @p_'s are the parameter names as distinguished from the stored procedure argument names.

If using a stored procedure return value (not a best practice), the call would look like:

command = new SqlCommand($@"EXECUTE @p_votecount = dbo.Votes @VotedMember = @p_VotedMember,
                                              @VotedBy = @p_VotedBy",  
                         StaticObjects._connection);

In both cases @p_votecount should be ParameterDirection.Output not ParameterDirection.ReturnValue. ReturnValue is only used with CommandType.StoredProcedure.

edited Apr 08 '19 at 21:19

answered Apr 08 '19 at 20:19

David Browne - Microsoft

80,331
6
39
67

I've included the SP i'm using in my original post now, and i am already using `ParameterDirection.Output`?, so what I've currently got `@@VotedMember` for example is wrong? – Danny Robins Apr 08 '19 at 20:32
It's not common practice to use `@@` but that's not the problem. The problem is that the output parameter does not appear in your SqlCommand.CommandText. – David Browne - Microsoft Apr 08 '19 at 20:38
just wanted to check as its always worked before, so i've updated the SqlCommand as suggested which i've tried before which throws error `System.Data.SqlClient.SqlException: Must declare the scalar variable "@@votecount".` this i assume is because i'm only passing the paramters for `@@VotedMember` & `@@VotedBy` however i'm not sure what to pass through for `@@votecount` – Danny Robins Apr 08 '19 at 20:44
SQL thinks `@@votecount` is a local variable because you haven't bound a parameter having that name. – David Browne - Microsoft Apr 08 '19 at 20:45
okay, so if i use `command.Parameters.Add("@votecount", SqlDbType.Int).Direction = ParameterDirection.Output` as the value for that parameter? apologies struggling with this one – Danny Robins Apr 08 '19 at 21:00
see edit for clarification. – David Browne - Microsoft Apr 08 '19 at 21:19
Thank you so much!, this is now working! – Danny Robins Apr 08 '19 at 21:37

C# to read OUTPUT value from stored procedure

1 Answers1