Why the function in ms sql server 2012 returns null?

Question

There is a function in MS SQL SERVER 2012 EXPRESS like below.

CREATE FUNCTION [dbo].[fnCheckClientAppVersion](@Version AS NVARCHAR(15))
RETURNS INT
AS
BEGIN
    DECLARE @VRS AS NVARCHAR(15);
    SET @VRS = (SELECT [Value] FROM Options WHERE [Key] = 'ClientAppVersion');
    IF @Version = @VRS
        RETURN 1;
    RETURN 0;
END

And I try to use call this function in my C# Code like below.

SqlCommand command = new SqlCommand();
command.Connection = Globals.Connection;
command.CommandText = "dbo.fnCheckClientAppVersion";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Version", "1.0.0.0"));
object o = command.ExecuteScalar();

But everytime when i run this code, the value of o is null.
Why?
What is the Problem?

You may find this useful http://stackoverflow.com/questions/6932199/executescalar-always-returns-null-when-calling-a-scalar-valued-function — Giorgos Betsos, Feb 05 '15 at 15:07

score 5 · Answer 1 · edited Feb 05 '15 at 15:16

5

A function is not a stored procedure. You may either alter your calling syntax to use 'SELECT dbo.fnCheckClientAppVersion....' and use CommandType.Text, or change your function to a stored procedure, and read the value from the result.

edited Feb 05 '15 at 15:16

Pரதீப்

91,748
19
131
172

answered Feb 05 '15 at 15:11

esmoore68

1,276
1
8
16

Yeah - generally you would not call a SQL function directly, but in combination with another SQL statement: SELECT Version,dbo.fnCheckClientVersion(Version) AS IsClientAppVersion FROM Table 1 – David P Feb 05 '15 at 15:23

score 0 · Answer 2 · answered Feb 05 '15 at 15:43

As suggested you should turn this into a procedure. You could also greatly simplify your query. Here is an example of how this might look:

create procedure CheckClientAppVersion
(
    @Version nvarchar(15)
) as
    set nocount on

    SELECT case when [Value] = @Version then 1 else 0 end as VersionMatch
    FROM Options 
    WHERE [Key] = 'ClientAppVersion'
GO

Why the function in ms sql server 2012 returns null?

2 Answers2