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 stackoverflow.com/questions/6932199/… — Giorgos Betsos
– Giorgos Betsos, Commented Feb 5, 2015 at 15:07

Pரதீப் · Accepted Answer · 2015-02-05 15:16:08Z

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 5, 2015 at 15:16

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

answered Feb 5, 2015 at 15:11

esmoore68

1,2961 gold badge9 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David P Over a year ago

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

Sean Lange · Accepted Answer · 2015-02-05 15:43:02Z

0

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

answered Feb 5, 2015 at 15:43

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Why the function in ms sql server 2012 returns null?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related