Return a string Value from SQL Server stored procedure

Question

I have the following method and stored procedure.

My question is how to return a string value from stored procedure to use in addStaffName method ?

public string addStaffName()
{
        string staffName = string.Empty;
        string sConnectionString = ConfigurationManager.ConnectionStrings["LGDB"].ToString();
        SqlConnection SqlCOn = new SqlConnection(sConnectionString);
        SqlCommand SqlCmd = new SqlCommand();

        SqlCOn.Open();
        SqlCmd.Connection = SqlCOn;
        SqlCmd.CommandType = CommandType.StoredProcedure;
        SqlCmd.CommandText = "FetchStaffName";
        SqlCmd.Parameters.AddWithValue("email", email);
        //???
        return staffName;
    }

create procedure fetchStaffName
    @email varchar(100)
AS 
begin 
    select (employeeName) 
    from employee 
    where email = @email
end

marc_s · Accepted Answer · 2013-07-14 15:56:50Z

3

If you can be sure that you'llonly ever get one row, one column as a result set - then you can use .ExecuteScalar() on your SqlCommand like this:

string staffName = string.Empty;

string sConnectionString = ConfigurationManager.ConnectionStrings["LGDB"].ToString();

using (SqlConnection sqlCon = new SqlConnection(sConnectionString))
using (SqlCommand sqlCmd = new SqlCommand("FetchStaffName", sqlCon)
{
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.Parameters.AddWithValue("@email", email);

    sqlCon.Open();

    staffName = sqlCmd.ExecuteScalar().ToString();

    sqlCon.Close();

    return staffName;
}

I also put the usage of SqlConnection and SqlCommand into using() { ... } blocks which is the recommended best practice for anything that's deriving from IDisposable to ensure proper disposal of the objects after their use

answered Jul 14, 2013 at 15:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

3 Comments

Iatrochemist Over a year ago

thank u so much , nice advice , i can not vote u up , cause i new here !

Iatrochemist Over a year ago

but what if i wanted to get two columns as result from stored procedure ?

marc_s Over a year ago

@AlirezaX: then you need to use .ExecuteReader() and you get back a SqlDataReader. With that you can read any number of columns, and any number of rows, and stick the results into objects of your choice

Ivan · Accepted Answer · 2013-07-14 16:30:48Z

1

Consider to make fetchStaffName a scalar function rather than stored procedure. By definition stored procedures are required to perform a set of actions with data. Scalar function guarantees there will be exactly one output value for the set of input values.

CREATE FUNCTION fetchStaffName (
 @email VARCHAR(100)
)
RETURNS VARCHAR(?) --Place the length of employeeName field instead of ? symbol.
BEGIN
    RETURN (SELECT employeeName FROM employee WHERE email = @email)
END

And then your .NET code transforms into the following:

using (SqlConnection SqlCOn = new SqlConnection(sConnectionString))
{
    SqlCommand SqlCmd = new SqlCommand("SELECT fetchStaffName(@email)", SqlCOn);
    SqlCmd.CommandType = CommandType.Text;
    SqlCmd.Parameters.AddWithValue("@email", email);

    SqlCOn.Open();
    staffName = (string)SqlCmd.ExecuteScalar();
}

As you can see CommandType changed from StoredProcedure to Text. I also wrapped the work with SqlConnection object into using construction to dispose its resources and close automatically.

edited Jul 14, 2013 at 16:30

answered Jul 14, 2013 at 16:25

Ivan

513 bronze badges

1 Comment

Ivan Over a year ago

There are several ways of doing so. You can use stored procedure with multiple output parameters (a little bit different .NET and T-SQL syntax). Also you can select a row with several columns inside stored procedure and then use ExecuteReader() method of your SqlCommand. For details create a new question.

Collectives™ on Stack Overflow

Return a string Value from SQL Server stored procedure

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related