Question 87996 in ASP.NET Question posted in
The official documentation can be found here.

Scalar valued function from SQL Server not returning value when called from C# – Asp.net

I was given this function

CREATE FUNCTION [dbo].[GET_WEBGIS_ISSUE_NUM]
    ()
RETURNS VARCHAR(50)
AS 
BEGIN
    DECLARE @v_new_num int, @v_new_issue_num varchar(50);
    
    SET @v_new_num = (SELECT COUNT(*) + 1 
                      FROM [dbo].[WEBGIS_ISSUE] 
                      WHERE [ISSUE_NUM] LIKE  CONCAT(FORMAT(GETDATE(), 'yyMM'), '%'));

    IF @v_new_num < 10 
        SET @v_new_issue_num = CONCAT(FORMAT(GETDATE(), 'yyMM'), '00', @v_new_num);

    ELSE IF @v_new_num < 100
        SET @v_new_issue_num = CONCAT(FORMAT(GETDATE(), 'yyMM'), '00', @v_new_num);

    ELSE
        SET @v_new_issue_num = CONCAT(FORMAT(GETDATE(), 'yyMM'), @v_new_num);
    
    RETURN @v_new_issue_num 
END;

I tried calling it from the following C# code

SqlConnection cnn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[NEPS].[dbo].[GET_WEBGIS_ISSUE_NUM]";

//add any parameters the stored procedure might require
if (cmd.Connection.State == ConnectionState.Closed) //cmd.Connection.Open();
{
    cnn.Open();
    var o = cmd.ExecuteScalar();
    //blabla
    cnn.Close();
}

but when I debug the code, I kept on receiving null.

Notes: the connection is ok, it is connected, when I tried changing the function’s name it yields an error and when I checked through the SQL Server it also returns an appropriate return value.

Tags:

2

Answers


  1. 0

    It is more common to use a SELECT statement to return a scalar function result. When you use EXECUTE (due to CommandType.StoredProcedure), you need to also specify a return parameter and retrieve the result from the parameter after execution:

    var result = cmd.Parameters.Add("@result", SqlDbType.VarChar, 50);
result.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery(); //ExecuteScalar will work too but the result is null and you still need to use the parameter
var o = result.Value;

    As noted in the comments to your question, consider the concurrency implications of this approach. Duplicate values will be returned until the row count changes.

    Login or Signup to reply.
  2. 0

    You’re treating a scalar function as a stored procedure, which is the wrong type for this type of execution. You need to ‘CommandType.Text’ with scalar functions.

    Other notes on the C# part :

    • use using blocks with SqlConnection and SqlCommand (let the using clause handles the dispose and close connection parts for you).
    • the query should be declared as const string
    • always end the query with a semicolon (even if it’s running in the SQL Server without it).
    • avoid using short names, choose a readable naming for your variables.

    Here is the C# code :

    const string query = "SELECT [NEPS].[dbo].[GET_WEBGIS_ISSUE_NUM]();";

using(SqlConnection connection = new SqlConnection(connectionString))
    using(SqlCommand command = new SqlCommand(query, connection))
    {
        connection.Open();
        var result = command.ExecuteScalar();

        // do stuff
    }

    For the function GET_WEBGIS_ISSUE_NUM perhaps you can avoid the extra IFs with this line :

    CREATE FUNCTION [dbo].[GET_WEBGIS_ISSUE_NUM]
    ()
RETURNS VARCHAR(50)
AS 
BEGIN
    DECLARE @v_new_num int, @v_new_issue_num varchar(50);
    
    SET @v_new_num = (SELECT COUNT(*) + 1 
                      FROM [dbo].[WEBGIS_ISSUE] 
                      WHERE [ISSUE_NUM] LIKE  CONCAT(FORMAT(GETDATE(), 'yyMM'), '%'));

    SET @v_new_issue_num, FORMAT(GETDATE(), 'yyMM') + RIGHT('000' + CAST(@v_new_num AS VARCHAR), 4);
    
    RETURN @v_new_issue_num 
END;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search