stored procedure returing a string for a C# app

oly 66 Reputation points
2022-04-15T20:46:06.663+00:00

Hello all;

I have the following stored procedure below

create procedure dbo.test_sp (@p_para varchar(10) out)
as
begin
set @p_para = select 'this is a test'
return @p_para

c# code below

using (Sqlconnection conn = new SqlConnection(getconnectionstring()))
using (Sqlcommand cmd = conn.CreateCommand())
{

cmd.CommandText = "dbo.test_sp";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(@p_para, SqlDBType.String);

SqlParameter retval = new SqlParameter(@Returnval, System.Data.SqlDbType.String);
retval.Direction = System.Data.ParameterDirection.ReturnValue;
cmd.Paramters.Add(retval);
conn.Open();

cmd.ExecuteNonQuery();
string str_retval = retval.value.ToString();

}

However, I noticed, it doesn't return anything in my str_retval when I run my application and I don't know why. Thanks in advanced.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113.8K Reputation points
    2022-04-19T17:49:18.707+00:00

    Try this C# code:

    using (Sqlconnection conn = new SqlConnection(getconnectionstring()))  
    using (Sqlcommand cmd = conn.CreateCommand())  
    {  
       cmd.CommandText = "dbo.test_sp";  
       cmd.CommandType = CommandType.StoredProcedure;  
      
       SqlParameter para = cmd.Parameters.Add("@p_para", SqlDbType.String, 10);  
       para.Direction = ParameterDirection.Output;  
      
       conn.Open();  
       cmd.ExecuteScalar();  
      
       string str_retval = (String)para.Value;  
      
       // . . .  
    }  
    
      
    

4 additional answers

Sort by: Most helpful
  1. Viorel 113.8K Reputation points
    2022-04-15T21:01:13.087+00:00

    Try something like this:

    create procedure dbo.test_sp (@p_para varchar(10) out)
    as
    begin
        set @p_para = 'this is a test'
    end
    
    . . .
    retval.Direction = System.Data.ParameterDirection.Output;
    . . .
    

  2. Naomi 7,361 Reputation points
    2022-04-15T21:37:07.74+00:00

    Don't return parameter using return @Paramjeet Dahiya statement. Instead define your parameter as Output parameter, e.g.
    in C# code use

    SqlParameter outVal = new SqlParameter('@p_para', System.Data.SqlDbType.VarChar, 30 );  
      
    outVal.Direction = ParameterDirection. Output;  
    cmd.ExecuteNonQuery();  
    

    string outValue = (string) outVal.Value;


  3. Bert Zhou-msft 3,421 Reputation points
    2022-04-18T03:21:45.667+00:00

    Hi,@oly

    Welcome to Microsoft T-SQL Q&A Forum!

    You can add a line of statement in c#, use ExecuteScalar, see here for related links.

    string p_para_ = SqlComm.ExecuteScalar();  
    

    As a reminder, it's best to use catch to catch errors in execution, which can be very useful for your debugging.
    and you need to distinguish between calling return values and returning strings, as Erland explained above.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. oly 66 Reputation points
    2022-04-19T14:12:56.56+00:00

    Hi All;

    Unfortunately, I am getting the error messsage in C# stating the procedure or function test_sp expects parameter @p_para which was not supplied, does anyone know what that means,