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, Oct 29 '19 at 05: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, Oct 29 '19 at 05: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, Oct 29 '19 at 05:34
Hi @ItsPete, sorry that 20336 was a typo and meant to be 20334! 20336 and 20334 are both classified as 'bespoke' items :) — brandito, Oct 29 '19 at 05:36
@jmcilhinney Without a return statement as the last line SQL server will give you the following error: `The last statement included within a function must be a return statement.` if that's what you mean? — brandito, Oct 29 '19 at 05:39
After some further narrowing down, I believe the problem is with the `GetIsBespoke` function in VB.NET — brandito, Oct 29 '19 at 05:42
@Brandito Take a look at [this answer](https://stackoverflow.com/a/17047099). It's in C#, but it should explain how to call an SQL user function. — ItsPete, Oct 29 '19 at 05:45
I wasn't talking about inside the `fncIsBespoke`. I specifically said in the SQL that calls that function. — jmcilhinney, Oct 29 '19 at 05:46
@jmcilhinney There isn't any SQL calling either functions provided, the VB.NET function calls the SQL function and that's it. — brandito, Oct 29 '19 at 05:47
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, Oct 29 '19 at 05: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, Oct 29 '19 at 06:04

score 1 · Accepted Answer · answered Oct 29 '19 at 05:55

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 '19 at 05:55

ItsPete

2,363
3
27
35

Returning boolean from SQL Server in VB.NET

1 Answers1