CODEWITHSUNDEEP
c#sql-serverselectstored-procedures
alex
alex

Reputation: 13

How to call a selected value from a stored procedure

If I have the following stored procedure, how do I call the selected value, aka CourseIdin my code so I can set it to a variable?

CREATE PROCEDURE [dbo].GetCourseIDFromCourseNumber(
 @courseNo int,
 @termId int,
 @courseId int OUTPUT
)
AS
BEGIN

SELECT @courseId = CourseId
FROM Course
Where (CourseNumber = @courseNo AND TermId = @termId)

END

C# code

   objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.CommandText = "GetCourseIDFromCourseNumber";

            objCommand.Parameters.AddWithValue("@courseNo", courseNumber);
            objCommand.Parameters.AddWithValue("@termId", term);
            objCommand.Parameters.AddWithValue("@section", section);

            //Create an Output parameter to store value from stored procedure
            SqlParameter courseIdParam = new SqlParameter("@courseId", SqlDbType.Int);
            courseIdParam.Direction = ParameterDirection.Output;
            objCommand.Parameters.Add(courseIdParam);

            //execute stored procedure
            DBConnect objDB = new DBConnect();
            SqlConnection connect = objDB.GetConnection();
            connect.Open();                                                   
            objCommand.ExecuteNonQuery();
            connect.Close();

            //read parameter value
            if (courseIdParam.Value != null)
            {
                var courseId = (int)courseIdParam.Value;
                Session["Course"] = courseId;
            }

GetDataSet method

public DataSet GetDataSetUsingCmdObj(SqlCommand theCommand)
{
        // Used for stored procedures (Select only) with parameters
        try
        {
            theCommand.Connection = myConnectionSql;
            SqlDataAdapter myDataAdapter = new SqlDataAdapter(theCommand);

            DataSet myDataSet = new DataSet();
            myDataAdapter.Fill(myDataSet);

            ds = myDataSet;
        }
        catch (Exception e)
        {
        }
        finally
        {
            myConnectionSql.Close();
        }

        return ds;
    }

Upvotes: 0

Views: 133

Answers (3)

Serge
Serge

Reputation: 4036

Since the poster added the relevant C# code, it became apparent that a stored procedure may be suitable.

If the stored procedure will return only one value (rather than a data-set), then it may be better to return the value as an OUTPUT parameter:

CREATE PROCEDURE [dbo].GetCourseIDFromCourseNumber(
@courseNo int,
@termId int,
@courseId int OUTPUT
)
AS
BEGIN

    SELECT @courseId = CourseId
    FROM Course
    Where (CourseNumber = @courseNo AND TermId = @termId)

END

Now in C#:

SqlCommand objCommand = new SqlCommand();
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.CommandText = "GetCourseIDFromCourseNumber";

objCommand.Parameters.AddWithValue("@courseNo", courseNumber);
objCommand.Parameters.AddWithValue("@termId", term);

// Create an OUTPUT parameter to store value from stored procedure
SqlParameter courseIdParam = new SqlParameter("@courseId", SqlDbType.Int);
courseIdParam.Direction = ParameterDirection.Output;
objCommand.Parameters.Add(courseIdParam);

// Execute stored procedure
objCommand.ExecuteNonQuery();

// Read parameter value
if(courseIdParam.Value != null)
{
    var courseId = (int)courseIdParam.Value;
}

Upvotes: 0

Chris Cruz
Chris Cruz

Reputation: 2029

I personally like to store the returned values from the stored procedure into a DataTable rather than a DataSet; however, this is entirely personal preference in this case. Then you will need to iterate through the data in the DataTable's rows.

Please see the following code:

string courseId;

DataTable myDataTable = new DataTable();
myDataAdapter.Fill(myDataTable);

foreach (DataRow dr in dt.Rows)
{
     courseId = dr["CourseId"].ToString();
}

Upvotes: 0

Serge
Serge

Reputation: 4036

Create a FUNCTION instead of the stored procedure:

CREATE FUNCTION [dbo].GetCourseIDFromCourseNumber(
@courseNo int,
@termId int
)
RETURNS TABLE
AS RETURN
(
    SELECT CourseId
    FROM Course
    Where (CourseNumber = @courseNo AND TermId = @termId)
)

You can now use the output in any query, for example:

SELECT CourseId
FROM dbo.GetCourseIDFromCourseNumber(123,1);

SELECT t.*
FROM myTable t
CROSS APPLY dbo.GetCourseIDFromCourseNumber(123,1) c
WHERE t.CourseId = c.CourseId

Upvotes: 1

Related Questions