CODEWITHSUNDEEP
c#sql-serverdatabase
namco
namco

Reputation: 6338

Why the function in ms sql server 2012 returns null?

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?

Upvotes: 0

Views: 88

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 0

esmoore68
esmoore68

Reputation: 1296

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.

Upvotes: 5

Related Questions