CODEWITHSUNDEEP
sql-servervb.netwebforms
brandito
brandito

Reputation: 708

Returning boolean from SQL Server in VB.NET

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?

Upvotes: 1

Views: 965

Answers (1)

ItsPete
ItsPete

Reputation: 2368

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

Upvotes: 1

Related Questions