none
Stored Procedure Output parameter RRS feed

  • Question

  • Hello,

    I have a stored procedure Member_data that has only 1 output parameter. Below is the stored procedure code.

    ALTER PROCEDURE usp_MemberData (@Mem_Name varchar(50) OUTPUT)
    AS
    BEGIN
    SELECT
    	@Mem_Name = Mem_Name
    	
    FROM
    	UnitedDiningClub.dbo.UDC_Member
    
    END
    GO
    EXEC usp_MemberData ''
    
    GO
    

    Now I have a method in c# that accesses the procedure but it is giving me the following error: "Procedure or function 'usp_MemberData' expects parameter '@Mem_Name', which was not supplied."

    Here is the method:

    public DataSet GetMembers()
            {
                DataSet myDataSet = new DataSet();
                SqlCommand cmdMembers = new SqlCommand();
                cmdMembers.CommandText = "usp_MemberData";
                cmdMembers.CommandType = CommandType.StoredProcedure;
                cmdMembers.Connection = jadcoreConnection.GetConnection();
    
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(cmdMembers);
                mySqlDataAdapter.Fill(myDataSet);                                //error begins here
                return myDataSet;
            }
    Thanks in advance.

    Tuesday, October 7, 2014 2:25 PM

Answers

  • You need to add the parameter even if it's output only just as @Uri suggested.

    If you want to fill a dataset change the stored procedure and remove the parameter so it'll return a dataset (datatable)

    ALTER PROCEDURE usp_MemberData
    AS
    BEGIN
    SELECT
    	Mem_Name
    	
    FROM
    	UnitedDiningClub.dbo.UDC_Member
    
    END


    "If there's nothing wrong with me, maybe there's something wrong with the universe!"

    • Proposed as answer by Donghui LiModerator Wednesday, October 8, 2014 1:57 AM
    • Marked as answer by Nonabona Wednesday, October 8, 2014 5:57 PM
    Tuesday, October 7, 2014 2:45 PM

All replies

  • command.Parameters("@param").Direction = ParameterDirection.Output
    
    
        command.ExecuteReader()
    
    
        variable = command.Parameters("@param").Value

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 7, 2014 2:38 PM
    Moderator
  • Hi Nonabona,

    Since your store procedure expect the parameter to passed in/passed out, you can;t just ignore it. You need to supply it to the stored procedure, it upto you to use the value or not. In your case just modify the code as such. 

    public DataSet GetMembers()
            {
                //variable to hold the output value
                string strMem_Name = string.Empty;
                DataSet myDataSet = new DataSet();
                SqlCommand cmdMembers = new SqlCommand();
                cmdMembers.CommandText = "usp_MemberData";
                //Add this 2 line of code
                cmdMembers.Parameters.Add(new SqlParameter("@Mem_Name", (object)strMem_Name));
                cmdMembers.Parameters[0].Direction = ParameterDirection.Output;
                //End of Code
                cmdMembers.CommandType = CommandType.StoredProcedure;
                cmdMembers.Connection = jadcoreConnection.GetConnection();
    
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(cmdMembers);
                mySqlDataAdapter.Fill(myDataSet);                                //error begins here
                return myDataSet;
            }

    Regards

    Harsh


    • Edited by Harsh Kumar Tuesday, October 7, 2014 2:47 PM edited
    Tuesday, October 7, 2014 2:44 PM
  • You need to add the parameter even if it's output only just as @Uri suggested.

    If you want to fill a dataset change the stored procedure and remove the parameter so it'll return a dataset (datatable)

    ALTER PROCEDURE usp_MemberData
    AS
    BEGIN
    SELECT
    	Mem_Name
    	
    FROM
    	UnitedDiningClub.dbo.UDC_Member
    
    END


    "If there's nothing wrong with me, maybe there's something wrong with the universe!"

    • Proposed as answer by Donghui LiModerator Wednesday, October 8, 2014 1:57 AM
    • Marked as answer by Nonabona Wednesday, October 8, 2014 5:57 PM
    Tuesday, October 7, 2014 2:45 PM
  • Thank you, fixing my store procedure fixed it. I understand now I just needed to fill the dataset.

    Although I am now getting a different error...

    Thanks

    Tuesday, October 7, 2014 3:20 PM
  • @Noabona : What kind of error are you getting ?

    "If there's nothing wrong with me, maybe there's something wrong with the universe!"

    Tuesday, October 7, 2014 3:30 PM
  • I am getting "Input string was not in the correct format" but I believe that problem is project related somewhere else.

    Thanks for all the help!

    Tuesday, October 7, 2014 3:52 PM