Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    395

    Angry Unanswered: 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)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •