Scalar valued function from SQL Server not returning value when called from C#

Question

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.

cmd.CommandType = CommandType.StoredProcedure? GET_WEBGIS_ISSUE_NUM isn't a Stored Procedure; it's a scalar function... — Thom A
– Thom A ♦, Commented Nov 29, 2021 at 10:21
You need to use CommandType.Text and run SELECT * FROM dbo.GET_WEBGIS_ISSUE_NUM() - but this really BADLY smells like a hack doing somewhat the same thing as an IDENTITY column - just not properly and safely........ — marc_s
– marc_s, Commented Nov 29, 2021 at 10:25
Perhaps a SEQUENCE would be a better choice if it is trying to serve a purpose similar to an IDENTITY but for multiple tables. SEQUENCE is available on all supported (included those in extended support) versions of SQL Server, so if that is what you are after I see no reason why you wouldn't use it. — Thom A
– Thom A ♦, Commented Nov 29, 2021 at 10:37
You need an actual query SELECT dbo.GET_WEBGIS_ISSUE_NUM(). Although why this function even exists is another question. At the very least it should be an inline Table Valued Function, which is much faster. You also need to dispose your connection and command objects with using. And you don't need if (cmd.Connection.State there is no reason for it to be open if you just created it — Charlieface
– Charlieface, Commented Nov 29, 2021 at 10:52
Last but not least, trying to generate a "unique" number using COUNT(*) + 1 is highly suspect, as it seems this would easily break if records are ever removed (even without concurrency), so you'd have to commit to a table that is absolutely append-only. An approach based on MAX seems more stable (but still not safe under concurrency on its own, mind you). — Jeroen Mostert
– Jeroen Mostert, Commented Nov 29, 2021 at 11:29

marc_s · Accepted Answer · 2021-12-01 20:10:44Z

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;

Dan Guzman · Accepted Answer · 2021-11-29 12:51:22Z

1

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.

edited Nov 29, 2021 at 12:51

answered Nov 29, 2021 at 11:22

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Collectives™ on Stack Overflow

Scalar valued function from SQL Server not returning value when called from C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related