How to use output parameter and select query result of a sql server stored procedure in ado.net?

Question

Hi I want to get the value of output parameter and as well as the result set of select query.
I used ExecuteNonQuery, it gives proper value for output parameter.
I used ExecuteReader it does not give proper value for output parameter but it gives proper value for select query.
So what should I use to get both the results.

    ALTER PROCEDURE [dbo].[XYZ] 
(  
 @szUserName varchar(50),  
 @iOutDistinceBankCount int out
)  
AS  
BEGIN  
declare @iCountDistinctBanks int;
set @iCountDistinctBanks = (select count (distinct a.DCC_BANK_ID )
 from DEF a with(nolock)
join ABC b with(nolock) on
 a.ROLEID = b.ROLEID
where b.USERNAME = @szUserName and b.STATUS_ID = 2)

if ((@iCountDistinctBanks > 1) or (@iCountDistinctBanks = 0))
    begin
        set @iOutDistinceBankCount = @iCountDistinctBanks 
    end

else
    begin
        set @iOutDistinceBankCount = 1;
            select a.DCC_BANK_ID as DCC_BANK_ID
            from DEF a with(nolock)
            join ABC b with(nolock) on
            a.ROLEID = b.ROLEID
            where b.USERNAME = @szUserName and b.STATUS_ID = 2
    end

END

This is my C# Code.

Int32 i32DistinctDCCBankCount = -1;
            Int64 i64BankStaticID = -1;
            InitDB();
            m_command = new SqlCommand("DCC_spUIDCCBankIdAccordingUser", m_con);
            m_command.Parameters.Add("@szUserName", System.Data.SqlDbType.VarChar, 50).Value = MerchantName;

            SqlParameter output = new SqlParameter("@iOutDistinceBankCount", System.Data.SqlDbType.Int);
            output.Direction = System.Data.ParameterDirection.Output;
            m_command.Parameters.Add(output);

            m_command.CommandType = System.Data.CommandType.StoredProcedure;
            m_con.Open();
           // m_reader = m_command.ExecuteReader();
            m_command.ExecuteNonQuery();
            i32DistinctDCCBankCount = Convert.ToInt32(m_command.Parameters["@iOutDistinceBankCount"].Value);


                    if (i32DistinctDCCBankCount == 0)
                    {
                        iDistinctDCCBankCount = 0;
                        return i32DistinctDCCBankCount;
                    }
                    else if (i32DistinctDCCBankCount > 1)
                    {
                        iDistinctDCCBankCount = i32DistinctDCCBankCount;
                        return -2;
                    }
                    else if (i32DistinctDCCBankCount == 1)
                    {
                        i64BankStaticID = Convert.ToInt64(m_reader["DCC_BANK_ID"]);
                        iDistinctDCCBankCount = i32DistinctDCCBankCount;
                        return i64BankStaticID;
                    }


            iDistinctDCCBankCount = 0;
            return 0;

Anant Dabhi · Accepted Answer · 2014-03-29 08:41:17Z

2

This same query can be executed directly with the Command.ExecuteReader (or ExecuteNonQuery if you don't have a rowset to process), but there are several other steps you'll need to take to process the returned values. Remember, you'll have to complete processing for all rowsets before attempting to capture the Return value or OUTPUT parameters. The following code shows how to use the ExecuteReader and a loop to process the rowsets, and then capture the Return value and OUTPUT parameters. You'll find that OUTPUT parameters (even a lot of them) can be handled far faster than even a single row of data returned by a SELECT.

here is example

    With cmd.Parameters
        cn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        ' Process rowset(s)
        bolEOF = dr.Read
        Do
            Do While bolEOF = True
                ' Process rows
                bolEOF = dr.Read()
            Loop
        Loop While dr.NextResult = True
        cmd.Cancel()
// you need to close dataReader first
        dr.Close()


        Debug.WriteLine("@iOutDistinceBankCount:" & _
            .Item("@iOutDistinceBankCount").Value.ToString)
    End With

answered Mar 29, 2014 at 8:41

Anant Dabhi

11.2k3 gold badges33 silver badges51 bronze badges

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

1 Comment

Vladislav Borovikov Over a year ago

why do you call Cancel method?

jenson-button-event · Accepted Answer · 2014-03-29 08:40:38Z

One possible way is to use ado's NextRecordSet and not use output parameter at all

if ((@iCountDistinctBanks > 1) or (@iCountDistinctBanks = 0))
    begin
        set @iOutDistinceBankCount = @iCountDistinctBanks 
        select @iOutDistinceBankCount as OutDistinceBankCount 
        select 0 where 0 = 1
    end

else
    begin
        set @iOutDistinceBankCount = 1;
        select @iOutDistinceBankCount as OutDistinceBankCount 

            select a.DCC_BANK_ID as DCC_BANK_ID
            from DEF a with(nolock)
            join ABC b with(nolock) on
            a.ROLEID = b.ROLEID
            where b.USERNAME = @szUserName and b.STATUS_ID = 2
    end

END

in code

get the first record set which is your parameter then rs = rs.NextRecordSet() ... read your rows

performance: output parameter vs select really is not a consideration here

Collectives™ on Stack Overflow

How to use output parameter and select query result of a sql server stored procedure in ado.net?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related