How to get SQL String result from a stored procedure and save it in a string variable in Windows C # Windows

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 

etc ... it continues as 200 lines of script with at least 10-12 variables

after that I want to get a 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 ...... 

so far I have managed to get this using the PRINT instruction, but I can’t take it in a variable in my C # codec, which looks 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; } } } } 

everything i get for result 0 or NULL or etc. I tried to return a value from SQL using PRINT, RETURN, SET, OUTPUT ......., but nothing works. However, fetching from C # to SQL looks like working with children. Any ideas?

Answer 1

If you want to return the concatenation string as output, then at the end of the procedure just write select @result . Be sure to bind it before this statement.

This will return you a string that you can use in C # code as a string.

Answer 2

Change the stored procedure as follows:

 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 change your code as follows:

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

Answer 3

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 output the text from the procedure and get it using C #