Returning a boolean from a T-SQL Stored Procedure
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 reas开发者_如何学JAVAonable?
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
Return a bit: "an integer data type that can take a value of 1, 0, or NULL."
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 ...?"...
精彩评论