Re: Trouble in getting a value from bit data type in stored procedure

Hi eveyone,

I'm trying to get the stored procedure to return a value from a field in a table. The value in the field stores a bit value and default value is set to 0. So there should always a value in that field but it is giving me a null value. Can anyone see why.
I'm calling sp thru vb. Thanks much!

This is VB:
sub
Set cancel_inspection_query = Nothing
With cancel_inspection_query
.ActiveConnection = CurrentProject.Connection
.CommandText = "spInspec_cancel_initial_scan1"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.inspec_id", adInteger, adParamInput, 4, Me!inspecid.Caption)
.Parameters.Append .CreateParameter("@.bag_num", adInteger, adParamInput, 4, Me!bag_num.Caption)
.Parameters.Append .CreateParameter("@.sampling_id", adInteger, adParamInput, 4, Me!rmr.Caption)
.Execute , , adExecuteNoRecords

End With
Debug.Print cancel_inspection_query("ret_val").Value
end sub

This is sp:

CREATE PROCEDURE dbo.spInspec_cancel_initial_scan1
@.inspec_id int,
@.sampling_id int,
@.bag_num int
AS
declare @.inspection_complete bit

SELECT @.inspection_complete = inspection_complete
FROM dbo.tblBag_results
WHERE bag_num = @.bag_num;
begin
if @.inspection_complete= 1
return(1)
Else
if @.inspection_complete = 0
return(100)
--else
--return(-1)The Table DDL would help.

Is Bag_Num a PK or unique index?

If not, that's a problem...

Also is the column defined as NOT NULL?

If not, that's a problem...

And why not use an OUTPUT variable instead?

You should let SQL Server manage the return value. I've seen times when it overrides your value...which could be a problem if you code for a particular value...