Returning a boolean from a T-SQL Stored Procedure..

Question

What's the most efficient way to return a boolean (true/false) out of a T-SQL Stored Procedure? I want it to do a query and return whether it succeeded or not. I'm calling via ASP.

Let me be a little more specific about what I'm doing.

If a record exists in a table (indicating a document is already reserved and can't be checked out), I want to notify the user on the front end. I'll determine that by checking Exists... in T-SQL and then somehow pushing that back to Classic ASP (return value, parameter, recordset field).

Does that make any answer more reasonable?

Answer 1

Return a bit: "an integer data type that can take a value of 1, 0, or NULL."

Answer 2

Not a good idea.

A return value, output parameter or a recordset will be undefined or not set or partial if you have an error. For example, a CAST error will abort the code (without TRY/CATCH).

A far better method will rely on Exception handling, like this:

BEGIN TRY
   ...
   --assume worked
END TRY
BEGIN CATCH
   DECLARE @foo varchar(2000)
   SET @foo = ERROR_MESSAGE()
   RAISERROR (@foo, 16,1)
END CATCH

However, I suspect I could be answering your later question about "why didn't SQL Server do ...?"...

Answer 3

I have this function in ASP that assume that the SP takes the last parameter as an integer output value.

Returning and integer is better, cause you can return several states, and not only true/false.

Function RunSPReturnInteger(strSP , params())
    On Error resume next

    ''// Create the ADO objects
    Dim cmd
    Set cmd = server.createobject("ADODB.Command")

    ''// Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc

    ''// propietary function that put the params in the cmd
    collectParams cmd, params

    ''// Assume the last parameter is outgoing
    cmd.Parameters.Append cmd.CreateParameter("@retval", adInteger, adParamOutput, 4)

    ''// Execute without a resulting recordset and pull out the "return value" parameter
    cmd.Execute , , adExecuteNoRecords
    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if
    RunSPReturnInteger = cmd.Parameters("@retval").Value

    ''// Disconnect the recordset, and clean up
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing

    Exit Function
End Function