title: How to get SQL String Result from Stored Procedure and save it in C# Windows Application string variable
body: <p>I have the following Stored Procedure :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and so on... it continues like 200 rows of script with at least 10-12 variables</p>
  
  <p>after that I want to get result like this </p>
  
  <pre><code>'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
  
  ......
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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;
              }
          }
      }
  }
  </code></pre>
  
  <p>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?</p>