How to get SQL String Result from Stored Procedure and save it in C# Windows Application string variable

Question

I have the following Stored Procedure :

ALTER PROCEDURE [dbo].[ProcedureName] 

    @date NVARCHAR(50)

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @result nvarchar(500) -- this one should return string.

    DECLARE @variable1 NVARCHAR(50)
    set @variable1 = (SELECT COUNT(*) FROM dbo.Table1 WHERE column1 not in (select column1 from dbo.Table2))

    DECLARE @variable2 NVARCHAR(50)
    update dbo.Table1 set columnX = 1 where column1 not in (select column1 from  dbo.Table2)

    set @variable2 = @@ROWCOUNT

and so on... it continues like 200 rows of script with at least 10-12 variables

after that I want to get result like this

'Hello,' + 

'Some Text here' + 

@date +': ' + 

'Explaining text for variable1- ' + @variable1 + 

'Updated rows from variable2 - ' + @variable2 + 

'some other select count - ' + @variable3 +

'some other update rowcount - '+ @variable4

......

till now i was able to get this with PRINT Statement, but can't take it to variable in my C# code which goes like this:

public void Execute_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("Are you sure you want to execute the program?", "Confirm Start", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.No)
    {
        string connectionString = GetConnectionString(usernamePicker.Text, passwordPicker.Text);
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("dbo.ProcedureName", connection))
            {
                connection.Open();
                cmd.CommandText = "dbo.ProcedureName";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@date", SqlDbType.VarChar).Value = dateTimePicker1.Text;

                SqlParameter result = cmd.Parameters.Add("@result", SqlDbType.VarChar);
                result.Direction = ParameterDirection.ReturnValue;

                cmd.ExecuteScalar();
                var resultout = (string)cmd.Parameters["@result"].Value;
                connection.Close();
                TextMessage.Text = dateTimePicker1.Text;
            }
        }
    }
}

all i get for result is 0 or NULL or etc. i tried to return value from SQL with PRINT, RETURN, SET, OUTPUT ....... but nothing seems to work. However fetching variable from C# to SQL seems like child-work. Any ideas?

Are you running a select at the end of your procedure to return the value? — Mikey Mouse
– Mikey Mouse, Commented May 15, 2013 at 8:35
You can use Output variable to assign the values of multiple variable — Deepak.Aggrawal
– Deepak.Aggrawal, Commented May 15, 2013 at 8:36
Take a look on Input/Output parameters and Return Value Did you return something to be placed in the @result parameter? — Steve
– Steve, Commented May 15, 2013 at 8:39
i am assigning final string 'Hello' + ....... to a result variable but i don't know how to output it like some kind of a message from procedure — user2384855
– user2384855, Commented May 15, 2013 at 8:58

Bhushan · Accepted Answer · 2013-05-15 09:13:03Z

6

If you want to return the concatenate string as output then at the end of the procedure just write select @result. Make sure that you have concatenated it before this statement.

This will return you the string which you can use in your c# code as a string.

answered May 15, 2013 at 9:13

Bhushan

3161 gold badge7 silver badges16 bronze badges

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

4 Comments

user2384855 Over a year ago

I did select @result at the end of procedure but when i run C# i get this : Unable to cast object of type 'System.Int32' to type 'System.String'. im trying to get it with : var resultout = (string)cmd.Parameters["@result"].Value; after cmd.ExecuteScalar(); but cmd.Parameters["@result"].Value is again 0

Bhushan Over a year ago

try string str=cmd.ExecuteScalar(); You don't need to access the output parameter.

Bhushan Over a year ago

you can remove those output parameters from your SP. That store procedure will directly return you the string.

user2384855 Over a year ago

string str = (string)cmd.ExecuteScalar(); that did the work, thank you so much. I was stuck at this point for two days ....

gzaxx · Accepted Answer · 2013-05-15 08:40:56Z

1

Change your stored procedure to this:

ALTER PROCEDURE [dbo].[ProcedureName] 

    @date NVARCHAR(50),
    @variable1 NVARCHAR(50) output,
    @variable2 NVARCHAR(50) output

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @result nvarchar(500) -- this one should return string.

    set @variable1 = (SELECT COUNT(*) FROM dbo.Table1 WHERE column1 not in (select column1 from dbo.Table2))

    update dbo.Table1 set columnX = 1 where column1 not in (select column1 from  dbo.Table2)

    set @variable2 = @@ROWCOUNT

and modify your code like this:

SqlParameter result1 = cmd.Parameters.Add("@variable1", SqlDbType.VarChar);
result1.Direction = ParameterDirection.ReturnValue;

SqlParameter result2 = cmd.Parameters.Add("@variable2", SqlDbType.VarChar);
result2.Direction = ParameterDirection.ReturnValue;

answered May 15, 2013 at 8:40

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

2 Comments

user2384855 Over a year ago

I've done that, but when i execute procedure i get exception like : Procedure or function 'ProcedureName' expects parameter '@result', which was not supplied.

gzaxx Over a year ago

This happens when procedure needs @result as parameter, but in your query and mine there is none so it certainly should not happen! Check your query and remove @result if it is not needed, or edit your post with actual live query, please :)

user2384855 · Accepted Answer · 2013-05-15 09:04:03Z

0

ok lets say I did this procedure

ALTER PROCEDURE [dbo].[ProcedureName] 

    @date NVARCHAR(50),
    @result nvarchar(500) output
AS

BEGIN

    SET NOCOUNT ON;


    DECLARE @variable1 NVARCHAR(50)
    set @variable1 = (SELECT COUNT(*) FROM dbo.Table1 WHERE column1 not in (select column1 from Table2))
    set @result = @variable1 + ' some text '

i want "@result" to be output text from procedure and get it with C#

answered May 15, 2013 at 9:04

user2384855

531 gold badge1 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to get SQL String Result from Stored Procedure and save it in C# Windows Application string variable

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related