stored procedure returns varchar c# sql server

Question

I need input one variable and output another

CREATE PROC CheckLogPas2
@log varchar(Max)
as
BEGIN
SET NOCOUNT ON;
DECLARE @k varchar(50);

set  @k = (SELECT position From sotrud_users where login = @log);

RETURN @k;
END

in this procedure i want take in variable "k" "position". "position" is varchar. When i try use it procedure give error. I can't output variable "k" because RETURN can't output not int variable

        SqlCommand sqlcmd = new SqlCommand("[CheckLogPas]", conn);
        sqlcmd.CommandType = CommandType.StoredProcedure;

        sqlcmd.Parameters.AddWithValue("@log", SqlDbType.Int);
        sqlcmd.Parameters["@UserID"].Value = DB_values.check_log;

        SqlParameter param = new SqlParameter();
        param.ParameterName = "@k";
        param.SqlDbType = SqlDbType.NVarChar;
        param.Direction = ParameterDirection.ReturnValue;

        sqlcmd.ExecuteNonQuery();
        comm.Parameters.Add(param);

        conn.Open();
        comm.ExecuteNonQuery();

        pos = param.Value;

and this my code in c#. i need to get a variable in another variable string on c#

Assuming you're talking [tag:sql-server] (note what the [tag:sql] tag has to say about products), the return type of a stored procedure is `int`. There are no choices about that. If you want to "return" something else you need to use an output parameter or a result set. — Damien_The_Unbeliever, Jun 29 '22 at 14:48
Use an *OUTPUT Parameter* instead, E.g. https://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net — Alex K., Jun 29 '22 at 14:48
@AlexK. when i try output with OUTPUT give error "OUTPUT @k; END" Incorrect syntax near the "OUTPUT" construct. — David, Jun 29 '22 at 14:54
Thats not the correct syntax. https://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/ — Alex K., Jun 29 '22 at 14:56
This may be simplified, but if your stored procedure truly does nothing but return a single value based on another value it's also worth considering making it a function (an inline table-valued function for best performance), making it simpler to invoke. As a bonus this would allow you to use it from other queries in a `CROSS APPLY`, something you can't do with a stored procedure. Of course if you anticipate adding complicated business logic, a function is not appropriate. — Jeroen Mostert, Jun 29 '22 at 14:59
@AlexK. it's correct ? CREATE PROC CheckLogPas2 log varchar(Max), k varchar OUTPUT as BEGIN SET NOCOUNT ON; set k = (SELECT position From sotrud_users where login = @log); RETURN k; END — David, Jun 29 '22 at 14:59
Does this answer your question? [Get output parameter value in ADO.NET](https://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net) — Charlieface, Jun 29 '22 at 14:59
Remove the `RETURN k;` line it's unnecessary, and doesn't return the value. You only need `@k varchar(50) OUTPUT` and `SET @k = ...` — Charlieface, Jun 29 '22 at 15:00
@Charlieface Unfortunately no. I need an example of varchar output in a variable in SQL — David, Jun 29 '22 at 15:02
This one then possibly? https://stackoverflow.com/questions/49029333/how-to-call-a-stored-procedure-that-has-two-output-parameters-from-another-store — Charlieface, Jun 29 '22 at 15:03
The idea of an `OUTPUT` variable is identical whether it is an `int`, a `varchar`, a `date` or anything else, @David ; the only difference is the datatype you define in `Parameters.Add` and the variable you have in your application to assign the `OUTPUT` parameter's value to. — Thom A, Jun 29 '22 at 15:06
@Charlieface yes It worked! But only the first letter is displayed. How to get the whole word? — David, Jun 29 '22 at 15:06
[Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) — Charlieface, Jun 29 '22 at 15:12
@Charlieface i understand my miss take. anther problem how i can get variable in c#? SqlCommand sqlcmd = new SqlCommand("[CheckLogPas]", conn); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@log", SqlDbType.Int); sqlcmd.Parameters["@UserID"].Value = DB_values.check_log; SqlParameter param = new SqlParameter(); param.ParameterName = "@k"; param.SqlDbType = SqlDbType.VarChar; param.Direction = ParameterDirection.ReturnValue; sqlcmd.Parameters.Add(param); pos_id = Convert.ToString(param.Value); — David, Jun 29 '22 at 15:26
Don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/). — SMor, Jun 29 '22 at 15:34
With AddWithValue, you are supposed to specify the value as 2nd parameter. You cannot specify the type, which is a known problem for AddWithValue — Hans Keﬆing, Jun 29 '22 at 15:36
Look at the first link I sent you `param.Direction = ParameterDirection.Output;` — Charlieface, Jun 29 '22 at 15:43

Ibrennan208 · Answer 1 · 2022-06-29T16:55:45.683

I believe a Scalar-valued Function would solve your problem, instead of a Stored Procedure. Using this you would be able to create a function in SQL Server that accepts parameters, and can return a value. If you wish to return more than one value from your function, you would need to use a Table-valued Function instead.

Here is the structure for a Scalar-valued function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

And here is how a Table-valued Function would be structured:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

Your current stored procedure:

CREATE PROC CheckLogPas2
@log varchar(Max)
as
BEGIN
SET NOCOUNT ON;
DECLARE @k varchar(50);

set  @k = (SELECT position From sotrud_users where login = @log);

RETURN @k;
END

Could be re-written as a Scalar-valued function like so:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION CheckLogPas2 
(
    -- Add the parameters for the function here
    @log    nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    
    RETURN (SELECT position From sotrud_users where login = @log)

END
GO

Using that, you could instead have your c# code execute the following query:

SELECT [dbo].[CheckLogPas2](@log);

Like so:

// using a command to retrieve
using (SqlCommand cmd = new SqlCommand("SELECT [dbo].[CheckLogPas2](@log);", conn))
{
     // format command
     cmd.Parameters.Add("@log", SqlDbType.NVarChar).Value = yourLogVariable;

     // get a sqlreader with the results from our query
     using (SqlDataReader reader = cmd.ExecuteReader())
     {
                            
          if(reader.Read())
          {
              string desiredStringResult = reader.GetString(0);
          }

     }
}

stored procedure returns varchar c# sql server

1 Answers1