Returning boolean from SQL Server in VB.NET

Question

Currently I have a function on an SQL Server which does a basic check and returns 1 (SQL Server Data Type of 'bit') if true and 0 (SQL Server Data Type of 'bit') if false

Here's what I currently have:

Public Shared Function GetIsBespoke(ByVal ProductId As Integer)
        Dim Res As Boolean = False

        Dim obj_SqlConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnectionString").ConnectionString)

        Dim obj_SqlCommand As New SqlCommand("[TBL].[fncIsBespoke]", obj_SqlConnection)
        obj_SqlCommand.CommandType = CommandType.StoredProcedure

        Dim ProductId_SqlParameter As New SqlParameter("@ProductId", SqlDbType.Int)
        ProductId_SqlParameter.Direction = ParameterDirection.Input
        ProductId_SqlParameter.Value = ProductId

        Dim Result_SqlParameter As New SqlParameter("@Result", SqlDbType.Bit)
        Result_SqlParameter.Direction = ParameterDirection.ReturnValue

        obj_SqlCommand.Parameters.Add(ProductId_SqlParameter)
        obj_SqlCommand.Parameters.Add(Result_SqlParameter)

        If Not IsDBNull(Result_SqlParameter.Value) Then
            Res = Result_SqlParameter.Value
        ElseIf IsDBNull(Result_SqlParameter.Value) Then
            Res = False
        End If

        Return Res

    End Function

USE [SRV]
GO
/****** Object:  UserDefinedFunction [TBL].[fncIsBespoke]    Script Date: 29/10/2019 2:46:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [TBL].[fncIsBespoke](@ProductID int)  
RETURNS bit   
AS   
-- Returns if product is from bespoke category
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SubCategory
    FROM Inventory.Products  
    WHERE ProductId = @ProductID  
     IF (@ret >= 665) AND (@ret <= 668) 
        RETURN 1;
    ELSE
        RETURN 0;

    RETURN NULL;
END;

If I call PRINT [TBL].[fncIsBespoke](20334) in SQL Server it returns 1

But when I call GetIsBespoke(20334) in VB.NET it returns false?

Your 2 examples are using different parameters. Is this a typo? If not, does PRINT [TBL].[fncIsBespoke](20336) return 1 or 0? — ItsPete
– ItsPete, Commented Oct 29, 2019 at 5:31
Also, your ElseIf IsDBNull(Result_SqlParameter.Value) Then can just be Else because you will already know that it is NULL because it failed the If condition. — ItsPete
– ItsPete, Commented Oct 29, 2019 at 5:33
I've never called a function in SQL that way but would you not need to actually use a RETURN statement in your SQL that calls the function? — jmcilhinney
– jmcilhinney, Commented Oct 29, 2019 at 5:34
Thanks @ItsPete if you'd like to make an answer about how I forgot to actually add SELECT to the command I'd be happy to mark it as accepted. — brandito
– brandito, Commented Oct 29, 2019 at 5:49
"the VB.NET function calls the SQL function and that's it". No, that's not it. VB code simply CAN'T call a SQL function. Your VB code executes a command that calls a SQL function and then does nothing with the value returned by it. You can't expect the ReturnValue parameter to have a value when the SQL code in the command has no RETURN statement. That's like having two VB functions where the first calls the second and assuming that the first will return the value from the second without having a Return statement of its own. — jmcilhinney
– jmcilhinney, Commented Oct 29, 2019 at 6:04

ItsPete · Accepted Answer · 2019-10-29 05:55:06Z

1

Modify your SQL in the SqlCommand to select the result of your function:

Dim obj_SqlCommand As New SqlCommand("SELECT [TBL].[fncIsBespoke]", obj_SqlConnection)

Further information

answered Oct 29, 2019 at 5:55

ItsPete

2,3694 gold badges28 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Returning boolean from SQL Server in VB.NET

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related