none
can i store "returned value of a stored procedure" in c# to a variable RRS feed

  • Question

  • alter procedure PopulateL2Subjects @selectedItem varchar(250), @Currentsubjectid @varchar(10) output
    as
    begin
    	select SubjectName from SubjectsL2 where TopicBelongstoL1 =
    	(select @Currentsubjectid  = SubjectID from Subjects
    		where SubjectName = @selectedItem)
    return @Currentsubjectid 
    End

    I want to catch the returned value from the procedure to a variable in C# with ado.net,

    Can i, i yes may i know how?

    Thanks in advance

    Saturday, June 30, 2012 3:07 PM

Answers

  • First, a stored procedure return value (RETURN statement) can only return an integer, not varchar data.  The best practice is to use a stored procedure return value only to indicate success or failure (zero success and non-zero for failure).  Data should be returned either via a resultset or as an output parameter (which you've declared).

    There are a couple of other issues and considerations with this stored procedure.  A SELECT statement cannot both return data and assign a variable values.  So the construct of the subquery is not valid.  Below is an example proc and C# code to achieve the desired result:

    CREATE PROCEDURE dbo.PopulateL2Subjects 
    	@selectedItem varchar(250)
    	,@Currentsubjectid varchar(10) output
    AS
    
    --this assumes only one row for a given subject name exists
    SELECT @Currentsubjectid = SubjectID 
    FROM dbo.Subjects
    WHERE SubjectName = @selectedItem;
    
    SELECT SubjectName 
    FROM dbo.SubjectsL2 
    WHERE TopicBelongstoL1 = @Currentsubjectid;
    
    RETURN @@ERROR;
    GO

    C# code:

                var command = new SqlCommand("dbo.PopulateL2Subjects", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@selectedItem", SqlDbType.VarChar, 250));
                command.Parameters["@selectedItem"].Value = "Subject1";
                command.Parameters.Add(new SqlParameter("@Currentsubjectid", SqlDbType.VarChar, 10));
                command.Parameters["@Currentsubjectid"].Direction = ParameterDirection.Output;
                
                connection.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Trace.WriteLine(reader.GetString(0));
                }
                Trace.WriteLine(command.Parameters["@Currentsubjectid"].Value);
                connection.Close();

    Alternatively, you could return all the needed data as a resultset using a join.  This would also return multiple subjects with the same name but different SubjectIDs.

    ALTER PROCEDURE dbo.PopulateL2Subjects 
    	@selectedItem varchar(250)
    AS
    
    SELECT L2.SubjectName, s.SubjectID 
    FROM dbo.SubjectsL2 AS L2
    JOIN dbo.Subjects AS s ON 
    	s.SubjectID = L2.TopicBelongstoL1
    WHERE s.SubjectName = @selectedItem;
    
    RETURN @@ERROR;
    GO

                var command = new SqlCommand("dbo.PopulateL2Subjects", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@selectedItem", SqlDbType.VarChar, 250));
                command.Parameters["@selectedItem"].Value = "Subject1";
                
                connection.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Trace.WriteLine(reader.GetString(0));
                    Trace.WriteLine(reader.GetString(1));
                }
                reader.Close();
                connection.Close();
                Trace.ReadLine();
                return;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, June 30, 2012 3:57 PM
  • It looks like you are retrieving the output parameter value before executing the query.  In ADO.NET, the output parameter will not be available until the command is executed and the resultsets processed.  You might try moving the retrieval of the output parameter value after the connection Close method.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, July 1, 2012 1:36 PM

All replies

  • First, a stored procedure return value (RETURN statement) can only return an integer, not varchar data.  The best practice is to use a stored procedure return value only to indicate success or failure (zero success and non-zero for failure).  Data should be returned either via a resultset or as an output parameter (which you've declared).

    There are a couple of other issues and considerations with this stored procedure.  A SELECT statement cannot both return data and assign a variable values.  So the construct of the subquery is not valid.  Below is an example proc and C# code to achieve the desired result:

    CREATE PROCEDURE dbo.PopulateL2Subjects 
    	@selectedItem varchar(250)
    	,@Currentsubjectid varchar(10) output
    AS
    
    --this assumes only one row for a given subject name exists
    SELECT @Currentsubjectid = SubjectID 
    FROM dbo.Subjects
    WHERE SubjectName = @selectedItem;
    
    SELECT SubjectName 
    FROM dbo.SubjectsL2 
    WHERE TopicBelongstoL1 = @Currentsubjectid;
    
    RETURN @@ERROR;
    GO

    C# code:

                var command = new SqlCommand("dbo.PopulateL2Subjects", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@selectedItem", SqlDbType.VarChar, 250));
                command.Parameters["@selectedItem"].Value = "Subject1";
                command.Parameters.Add(new SqlParameter("@Currentsubjectid", SqlDbType.VarChar, 10));
                command.Parameters["@Currentsubjectid"].Direction = ParameterDirection.Output;
                
                connection.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Trace.WriteLine(reader.GetString(0));
                }
                Trace.WriteLine(command.Parameters["@Currentsubjectid"].Value);
                connection.Close();

    Alternatively, you could return all the needed data as a resultset using a join.  This would also return multiple subjects with the same name but different SubjectIDs.

    ALTER PROCEDURE dbo.PopulateL2Subjects 
    	@selectedItem varchar(250)
    AS
    
    SELECT L2.SubjectName, s.SubjectID 
    FROM dbo.SubjectsL2 AS L2
    JOIN dbo.Subjects AS s ON 
    	s.SubjectID = L2.TopicBelongstoL1
    WHERE s.SubjectName = @selectedItem;
    
    RETURN @@ERROR;
    GO

                var command = new SqlCommand("dbo.PopulateL2Subjects", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@selectedItem", SqlDbType.VarChar, 250));
                command.Parameters["@selectedItem"].Value = "Subject1";
                
                connection.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Trace.WriteLine(reader.GetString(0));
                    Trace.WriteLine(reader.GetString(1));
                }
                reader.Close();
                connection.Close();
                Trace.ReadLine();
                return;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, June 30, 2012 3:57 PM
  • Thank you Dan Guzman,

    Here i got a problem when i implement your suggesion, that is

    the procedure is only returning 'null' with ado.net

    but when i implement same with SQL server i'm getting output. here is code for SQL server

    alter procedure PopulateL2Subjects @selectedItem varchar(250), @id varchar(10) output
    as
    begin	
    	select @id=SubjectID from Subjects where SubjectName = @selectedItem	
    	select SubjectName from SubjectsL2 where TopicBelongstoL1 = @id	
    End
    
    declare @id varchar(10)
    exec PopulateL2Subjects Computers, @id output
    print @id
    

    here is code of ado.net

                SqlCommand cmd = new SqlCommand(sp, sqlcon);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter param, p1;
                
                param = cmd.Parameters.Add("@selectedItem", SqlDbType.VarChar, 250);
                p1 = cmd.Parameters.Add("@id", SqlDbType.VarChar, 10);
                cmd.Parameters[0].Value = selectitemfrom.SelectedValue.Trim();
                cmd.Parameters["@id"].Direction = ParameterDirection.Output;
                 st= (string)cmd.Parameters["@id"].Value;
    
    		// here st is returning null, st is a string	
                sqlcon.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                dlnew.DataSource = dar;
                if (dar.Read())
                {
                    dlnew.DataTextField = "SubjectName";
                    dlnew.DataValueField = "SubjectName";
                    dlnew.DataBind();
                    sqlcon.Close();
                    return true;
                }
                else
                {
                    sqlcon.Close();
                    return false;
                } 

    Sunday, July 1, 2012 6:12 AM
  • It looks like you are retrieving the output parameter value before executing the query.  In ADO.NET, the output parameter will not be available until the command is executed and the resultsets processed.  You might try moving the retrieval of the output parameter value after the connection Close method.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, July 1, 2012 1:36 PM