Re: Need sproc to return a recordset and then update a record

Question

Re: Need sproc to return a recordset and then update a record

AJ-92543

SSC Eights!

Points: 852
More actions
April 14, 2005 at 10:48 am

#164172

Hi everyone,
I created a sproc and I want it to return a recordset the first time and then update the table the second time but it'll only return the recordset and not update. Why?
Thanks for your assistance.
CREATE PROCEDURE dbo.spMfg_dates
@lot_n int,
@earliest_mfg int,
@latest_mfg int,
@opt int
AS
if @opt = 0
SELECT earliest_mfg_dt, latest_mfg_dt FROM dbo.tblFinal_inspection WHERE lotnum = @lot_n
return(1)
if @opt = 1
if @earliest_mfg > 0
  update tblFinal_inspection set earliest_mfg_dt = @earliest_mfg WHERE lotnum = @lot_n
--if @latest_mfg <> null
  --update tblFinal_inspection set latest_mfg_dt = @latest_mfg WHERE lotnum = @lot_n
GO
cmd_click()
opt = 0 'get_mfg_dt = True

    With cmd_mfg_dt
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "spMfg_dates"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
        .Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n)
        .Parameters.Append .CreateParameter("@earliest_mfg", adInteger, adParamInput, 4, earliest_mfg)
        .Parameters.Append .CreateParameter("@latest_mfg", adInteger, adParamInput, 4, latest_mfg)
        .Parameters.Append .CreateParameter("@opt", adInteger, adParamInput, 4, opt)
        Set rs_mfg_dt = .Execute
    End With
get_mfg_dt = False
    wk_range_num = 3        'the # of wks allow for add on FI
    curr_range = (rs_mfg_dt(1).Value - rs_mfg_dt(0).Value) + 1        'include a current week in the two range
    bal_range = wk_range_num - curr_range



    If bal_range = 0 Then
       'do some comparisons
    else
         'do some comparisons
End If
    rs_mfg_dt.close



'End If
If opt = 1 Then
Set cmd_mfg_dt = Nothing
With cmd_mfg_dt
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "spMfg_dates"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
        .Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n)
        .Parameters.Append .CreateParameter("@earliest_mfg", adInteger, adParamInput, 4, earliest_mfg)
        .Parameters.Append .CreateParameter("@latest_mfg", adInteger, adParamInput, 4, latest_mfg)
        .Parameters.Append .CreateParameter("@opt", adInteger, adParamInput, 4, opt)
        .Execute , , adExecuteNoRecords
    End With
End If

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ron k SSCertifiable Points: 6103 More actions · Answer 1

From BOL:

...Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement. To define a statement block, use the control-of-flow keywords BEGIN and END

Without a BEGIN / END block

if @opt = 0

SELECT earliest_mfg_dt, latest_mfg_dt FROM dbo.tblFinal_inspection WHERE lotnum = @lot_n

return(1) <--- This Always gets executed!

AJ-92543 SSC Eights! Points: 852 More actions · Answer 2

Thanks Ron for your reply.

I did left out BEGIN/END by mistake but I also thought if the @opt variable is 1 the second select statement will execute. Thanks for your explanations.