Return a message from a stored procedure to C# app

Question

I have a stored procedure that adds a user and at each permission I add, I want to start building a success message. My stored procedure runs fine but how do I get that success message back into a message dialog in my app? I want to display the below @text in a messagebox in my C# app. ``` DECLARE @text NVARCHAR(1000) SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.' SELECT @text ``` This is my call in my C# app: ``` public DataTable CreateOrDropUser(string dataBase, string procedure, SqlParameter[] parameters) { try { if (dataBase.Length > 0) { procedure = dataBase + ".." + procedure; } //Set procedure to DBNAME..ProcedureName SqlCommand cmd1 = new SqlCommand(procedure, con); cmd1.CommandType = CommandType.StoredProcedure; foreach (SqlParameter p in parameters) { if (p != null) { cmd1.Parameters.Add(p); } } con.Open(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd1); da.Fill(dt); con.Close(); MessageBox.Show("Success"); //This should d…

Accepted Answer

Your best bet is to use a output parameter.

In your stored procedure add the parameter `@text nvarchar(1000) OUTPUT` then in your code add an extra parameter with the name `@text` and set the parameter direction to `output`.

then just add the line `SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'` in your stored procedure

Edit: My answer is if you don't want this to affect your current query, if i misinterpreted your question please let me know. Also to get the value, after you execute the query you can get the value from the `@name` parameter using .Value

Edit 2: Example Code Should look something like

```
//Add these lines
SqlParameter text = new SqlParameter("@name", SqlDbType.NVarChar);
text.Direction = ParameterDirection.Output;
cmd1.Parameters.Add(text);

con.Open();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd1);
da.Fill(dt);
con.Close();

//Change this line
MessageBox.Show(text.Value); //This should display the @text variable in my proc
```

if you need help with the stored procedure please post it and i'll give a example with that too

Edit 3: Quick example Tested with a quick example. The C# code:

```
        using (SqlConnection connection = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=TestDB;Integrated Security=True"))
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "Test";

SqlParameter text = new SqlParameter("@Text", SqlDbType.NVarChar, 1000);
                text.Direction = ParameterDirection.Output;
                command.Parameters.Add(text);

using (DataTable dt = new DataTable())
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(command))
                    {
                        da.Fill(dt);
                    }
                }

Trace.WriteLine(text.Value);

connection.Close();
            }
        }
```

The Stored Procedure:

```
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Test
    @Text Nvarchar(1000) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

SET @Text = 'test'
END
GO
```

Worked fine for me if you want to check it against yours for differences

Edit 4: In your stored procedure the @text needs to be a parameter so instead of

```
ALTER PROCEDURE [dbo].[AdminDevUserCreate]
    @SQLLoginName varchar(50),
    @SQLLoginPass varchar(50) 
AS

DECLARE @text NVARCHAR(1000)OUTPUT  
```

make it

```
ALTER PROCEDURE [dbo].[AdminDevUserCreate]
    @SQLLoginName varchar(50),
    @SQLLoginPass varchar(50),
    @text NVARCHAR(1000) OUTPUT 
AS
```

also when creating the SqlParameter use

```
SqlParameter text = new SqlParameter("@Text", SqlDbType.NVarChar, 1000);
```

which should get rid of the size issue as you are telling it that the parameter is `NVARCHAR(1000)`

the line

```
PRINT @text
Select @text
```

shouldn't be needed

Return a message from a stored procedure to C# app

Solution

Problem