SQL Stored Procedures failing to return values

Question

I am working on a Tag system for a news page designed in ASP.NET. For the system I require a TagExists method to check for tags within the database. The stored procedure I have written is below.

ALTER PROCEDURE [dbo].[Tags_TagExists](
    @Tag varchar(50))
AS
BEGIN
    If (EXISTS(SELECT * FROM dbo.Tags WHERE LOWER(@Tag) = LOWER(Tag)))
        RETURN 1
    ELSE
        RETURN 0
END

When I call this method however 0 is always returned. I am using the following code to call the method

Public Shared Function TagExists(ByVal name As String) As Boolean
    Dim result As Boolean
    Using conn As SqlConnection = New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand("Tags_TagExists", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Tag", name)
        conn.Open()
        result = Convert.ToBoolean(cmd.ExecuteScalar())
        conn.Close()
    End Using
    Return result
End Function

I have tried switching the procedure to return 0 if the tag exists and 1 if it does not and it still returns 0 despite the exact same testing conditions. I have also returned the actual select query and it has complained of the Tag "news" (my test item) not being an int on execution showing the select itself is definitely properly formed.

If anyone can shed some light on this, Thanks Michael

Answer 1

It should probably be a function, but here is the stored proc code:

ALTER PROCEDURE [dbo].[Tags_TagExists](
    @Tag varchar(50))
AS
BEGIN
    If EXISTS(SELECT 1 FROM dbo.Tags WHERE LOWER(@Tag) = LOWER(Tag))
        BEGIN
            SELECT 1
        END    
    ELSE
        BEGIN
            SELECT 0
        END
END

Answer 2

Hi Mikey,

Please try using SELECT 1 and SELECT 0 instead of RETURN statement

Hope that helps,

Answer 3

You're returning from a Stored Procedure, not getting a single scalar value from a SQL statement.

I'm assuming this is a simple example and you have other processing you want to handle inside the Stored Procedure. In that case, using the Stored Procedure and return value is the right way to go. You need to handle the return value from the Stored Procedure in your C# code (Please excuse any syntax errors, my VB.NET is a bit rusty):

Public Shared Function TagExists(ByVal name As String) As Boolean
    Dim result As Boolean
    Using conn As SqlConnection = New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand("Tags_TagExists", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Tag", name).

        Dim retVal As SqlParameter = _
            cmd.Parameters.Add("return_value", SqlDbType.Int)
        retval.Direction = ParameterDirection.ReturnValue

        conn.Open()
        cmd.ExecuteNonQuery()

        result = System.Convert.ToBoolean(retval.Value)

        conn.Close()
    End Using
    Return result
End Function

If you're strictly interested in the return value and your Stored Procedure isn't performing any other use, then convert it to a simple select statement (or function). Your use of ExecuteScalar would work in that case.