Get String Value Returned From SQL Server

Question

I am using C# to run a SQL Server Stored Procedure, I want to store the returned variable from my sql stored procedure in a string variable, but every time I try to run it, my C# syntax errors telling me it is unable to convert varchar to int. I am very new when it comes to communicating with C# and SQL so please forgive me if I omit needed information or am overlooking the most obvious points. Below is my code, please informe me if any additional code is needed to further assist me in this issue. Thank you in advance for all who assist

//C# Syntax
private void btnBTNBTN_Click()
{
    string returnedvariable = DoThisStuff(abcd, faraca);
}

public string DoThisStuff(string abcd, int faraca)
{
    string value = string.Empty; 
    sqlsyntaxxxx = new StringBuilder();
    sqlsyntaxxxx.Append("exec dbo.AlphaDawg ");
    sqlsyntaxxxx.Append("'" + faraca + "'");
    _dataSet = RUNSQL(abcd, sqlsyntaxxxx.ToString());
    return value;
}

public DataSet RUNSQL(string abcd, string sqlsyntaxxxx)
{          
    _connectionString = System.Configuration.ConfigurationManager.AppSettings[connectionString].ToString();     
    _sqlDatabaseConnection = new SqlConnection(_connectionString);
    _sqlCommand = new SqlCommand(sqlsyntaxxxx, _sqlDatabaseConnection);
    _sqlDatabaseConnection.Open();
    _dataSet = new DataSet();
    _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);            
    _sqlDataAdapter.Fill(_dataSet, "Data");
    return _dataSet;
}

SQL Stored Procedure

ALTER PROCEDURE [dbo].[AlphaDawg]
(
    @faraca int
)
AS
BEGIN
    DECLARE @returnvalue varchar(500)

    UPDATE [dbo].[redllama]
    SET [AZYXIE] = '682134'+[zoneNumber]
    WHERE faraca = @faraca

    Set @returnvalue = (Select [AZYXIE] from [dbo].[redllama] WHERE faraca = @faraca)

    return @returnvalue
END

Answer 1

Your paramater variable faraca is defined as an int. So you need to change this line:

sqlsyntaxxxx.Append("'" + faraca + "'");

to

sqlsyntaxxxx.Append(faraca.ToString());

Also, you will have to set the value of value in your method DoThisStuff. As it is now... you are going to always just return an empty string. So you need to pull a field out of your result set before the return statement... something like:

value = ds.Tables[0].Rows[0]["Myfield"]

Then change your stored procedure to:

ALTER PROCEDURE [dbo].[AlphaDawg]
(
    @faraca int
)
AS
BEGIN
    DECLARE @returnvalue varchar(500)

    UPDATE [dbo].[redllama]
    SET [AZYXIE] = '682134'+[zoneNumber]
    WHERE faraca = @faraca

    Select [AZYXIE] 
    from [dbo].[redllama] 
    WHERE faraca = @faraca

END

Answer 2

First, the SQL return statement can only return an integer from a procedure and that value isn't part of any dataset.

In any event, return is not what you want, even for integers 99% of the time (it's intended as a status value for other SQL procedures). You usually return data through a dataset by executing a SELECT ... in your procedure. You can also return single values through an output parameter, but I would recommend the dataset until you are more familiar with this.

Change your SQL procedure as follows and it should work:

ALTER PROCEDURE [dbo].[AlphaDawg]
(
    @faraca int
)
AS
BEGIN
    DECLARE @returnvalue varchar(500)

    UPDATE [dbo].[redllama]
    SET [AZYXIE] = '682134'+[zoneNumber]
    WHERE faraca = @faraca

    Select [AZYXIE] from [dbo].[redllama] WHERE faraca = @faraca)
END

Hmm, looks like your c# code needs to be changed a little bit also. I haven't done this in a while, but I think that this is what you want:

public string DoThisStuff(string abcd, int faraca)
{
    string value = string.Empty; 
    sqlsyntaxxxx = new StringBuilder();
    sqlsyntaxxxx.Append("exec dbo.AlphaDawg ");
    sqlsyntaxxxx.Append("'" + faraca + "'");
    _dataSet = RUNSQL(abcd, sqlsyntaxxxx.ToString());

    value = _dataSet.Tables[0].Rows[0]["AZYXIE"].ToString();
    return value;
}