Use @return_value to UPDATE a table

  • Hello,

    I am trying to update a table using the returned value from a stored procedure, but I am not sure how to do this...

    I am trying to do this:

    USE [TransactionalData]

    GO

    DECLARE @return_value int

    declare @itm_cd varchar (30)

    declare @del_doc_num varchar (30)

    declare @ItemCode varchar (30)

    declare @DelDoc varchar (30)

    set @itm_cd = (select itm_cd from temp_sales_order_cbo where itm_cd = 'S270740')

    set @del_doc_num = (select del_doc_num from temp_sales_order_cbo where itm_cd = 'S270740')

    EXEC @return_value = [dbo].[COB]

    @ItemCode = N'S270740',

    @DelDoc = N'0625117GWJK'

    update temp_sales_order_cbo

    set received_date = @return_value

    where itm_cd = @ItemCode

    and del_doc_num = @DelDoc

    This doesn't work though... any help would be greatly appreciated.

    Thanks,

    ~D

  • First, @return_value is defined as an integer, but you seem to be putting it into a date column.

    Second, I'd have to see the definition of the COB proc to tell you anything useful about debugging that part of it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you very much... here is the sp:

    USE [TransactionalData]

    GO

    /****** Object: StoredProcedure [dbo].[COB] Script Date: 08/30/2011 16:12:57 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[COB]

    @ItemCode varchar(50) = null,

    @DelDoc varchar(50) = null

    AS

    declare @so_sql varchar (8000);

    declare @rp_sql varchar (8000);

    declare @gers_sql varchar (8000);

    declare @po_sql varchar (8000);

    declare @PreAdviceID varchar(255);

    declare @NetWarehouse int;

    declare @PoNum varchar(20);

    declare @PoDate datetime;

    declare @PoCode varchar(255);

    declare @InnerDelDocNum varchar(50)

    declare @InnerItemCode varchar(50)

    declare @InnerQtyDue int

    declare @InnerWrittenDate datetime

    declare @InnnerZoneCode varchar(1)

    declare @InnerDeliveryDate datetime

    create table #tempTableRP(ItemCode varchar(20), QtyDue decimal(15,6), DueDate datetime, PreAdviceID varchar(255))

    create table #tempTableSO(DelDocNum varchar(14), ItemCode varchar(9), QtyDue decimal(15,6), WrittenDate datetime, ZoneCode varchar(1), DeliveryDate datetime )

    create table #tempTableGers(NetAvailable int)

    create table #tempTablePO(PO_Code varchar(13), ItemCode varchar(9), QtyDue decimal(15,6), ArrivalDate datetime )

    create table #tempTableFinalAssigned(DateType varchar(1), ReceiveDate datetime)

    set @rp_sql = ' SELECT * FROM OPENQUERY ( RPL, '' SELECT '

    set @rp_sql = @rp_sql + ' l.sku_id, '

    set @rp_sql = @rp_sql + ' nvl(sum(l.qty_due), 0), '

    set @rp_sql = @rp_sql + ' h.due_dstamp, '

    set @rp_sql = @rp_sql + ' l.pre_advice_id '

    set @rp_sql = @rp_sql + ' from upi_receipt_line l, upi_receipt_header h '

    set @rp_sql = @rp_sql + ' where h.pallet_id = l.pallet_id and '

    set @rp_sql = @rp_sql + 'h.supplier_id like ''''2%'''' and '

    set @rp_sql = @rp_sql + 'to_char(h.due_dstamp, ''''hh:mi:ss am'''') <> ''''12:00:00 am'''' and '

    set @rp_sql = @rp_sql + ' l.pre_advice_id is not null and'

    set @rp_sql = @rp_sql + 'l.sku_id = (''''' + @ItemCode + ''''' ) '

    set @rp_sql = @rp_sql + ' group by l.sku_id, h.due_dstamp, h.supplier_id, l.pre_advice_id '

    set @rp_sql = @rp_sql + ' order by h.due_dstamp '

    set @rp_sql = @rp_sql + ' '' ) '

    insert into #tempTableRP exec ( @rp_sql )

    set @so_sql = ' SELECT * FROM OPENQUERY ( LIVE, '' Select '

    set @so_sql = @so_sql + 's.del_doc_num, '

    set @so_sql = @so_sql + 'sl.itm_cd, '

    set @so_sql = @so_sql + ' nvl(sum(sl.qty), 0), '

    set @so_sql = @so_sql + ' s.so_wr_dt, '

    set @so_sql = @so_sql + ' case when substr(s.ship_to_zone_cd, 1, 1) = ''''Z'''' then '

    set @so_sql = @so_sql + 'case when s.pu_del_dt <= sysDate + 21 then ''''D'''' '

    set @so_sql = @so_sql + ' when s.pu_del_dt = ''''05/DEC/2049'''' then ''''D'''' '

    set @so_sql = @so_sql + 'else ''''N'''' '

    set @so_sql = @so_sql + ' end '

    set @so_sql = @so_sql + 'else '

    set @so_sql = @so_sql + 'case when s.pu_del_dt <= sysDate + 14 then ''''D'''' '

    set @so_sql = @so_sql + ' when s.pu_del_dt = ''''05/DEC/2049'''' then ''''D'''' '

    set @so_sql = @so_sql + ' else ''''N'''' '

    set @so_sql = @so_sql + ' end '

    set @so_sql = @so_sql + ' end, '

    set @so_sql = @so_sql + ' s.pu_del_dt '

    set @so_sql = @so_sql + ' from so s, '

    set @so_sql = @so_sql + ' so_ln sl '

    set @so_sql = @so_sql + ' where sl.itm_cd = (''''' + @ItemCode + ''''') and '

    set @so_sql = @so_sql + ' s.del_doc_num = sl.del_doc_num and '

    set @so_sql = @so_sql + ' s.stat_cd = ''''O'''' and '

    set @so_sql = @so_sql + ' s.ord_tp_cd = ''''SAL'''' and '

    set @so_sql = @so_sql + ' sl.loc_cd is null and '

    set @so_sql = @so_sql + ' s.pu_del_dt != ''''31-DEC-49'''' and '

    set @so_sql = @so_sql + ' sl.void_flag = ''''N'''' '

    set @so_sql = @so_sql + ' group by s.del_doc_num, sl.itm_cd, s.so_wr_dt, s.ship_to_zone_cd, s.pu_del_dt '

    set @so_sql = @so_sql + ' order by s.so_wr_dt asc '

    set @so_sql = @so_sql + ' '' ) '

    insert into #tempTableSO exec ( @so_sql )

    set @gers_sql = ' SELECT * FROM OPENQUERY ( LIVE, '' Select '

    set @gers_sql = @gers_sql + '(select nvl(sum (f.qty),0) '

    set @gers_sql = @gers_sql + ' from itm_fifl f '

    set @gers_sql = @gers_sql + ' where i.itm_cd = f.itm_cd '

    set @gers_sql = @gers_sql + ' and f.store_cd = ''''06'''') '

    set @gers_sql = @gers_sql + ' from itm i '

    set @gers_sql = @gers_sql + ' where i.itm_cd = (''''' + @ItemCode + ''''') '

    set @gers_sql = @gers_sql + ' '' ) '

    insert into #tempTableGers exec ( @gers_sql )

    set @po_sql = ' SELECT * FROM OPENQUERY ( LIVE, '' Select '

    set @po_sql = @po_sql + 'p.po_cd, '

    set @po_sql = @po_sql + 'pl.itm_cd, '

    set @po_sql = @po_sql + ' sum(pl.qty_ord) - '

    set @po_sql = @po_sql + '(select nvl(sum(ph.qty), 0) '

    set @po_sql = @po_sql + 'from po_ln$actn_hst ph '

    set @po_sql = @po_sql + 'where ph.po_cd = p.po_cd and '

    set @po_sql = @po_sql + ' ph.po_actn_tp_cd = ''''RCV'''' and '

    set @po_sql = @po_sql + ' pl.ln# = ph.ln#), '

    set @po_sql = @po_sql + 'pl.arrival_dt '

    set @po_sql = @po_sql + ' from po p, '

    set @po_sql = @po_sql + ' po_ln pl '

    set @po_sql = @po_sql + ' where pl.itm_cd in (''''' + @ItemCode + ''''') and '

    set @po_sql = @po_sql + ' p.po_cd = pl.po_cd and '

    set @po_sql = @po_sql + ' p.stat_cd = ''''O'''' and '

    set @po_sql = @po_sql + ' pl.qty_ord > 0 and '

    set @po_sql = @po_sql + ' p.store_cd = ''''06'''' '

    set @po_sql = @po_sql + ' group by p.po_cd, pl.itm_cd, pl.ln#, pl.arrival_dt '

    set @po_sql = @po_sql + ' '' ) '

    insert into #tempTablePO exec ( @po_sql )

    set @NetWarehouse = (select NetAvailable from #tempTableGers)

    update #tempTablePO set QtyDue = 0 where PO_Code in (select substring(PreAdviceID, 5, 20) from #tempTableRP)

    declare cursor_po cursor for

    select * from #tempTableSO where ZoneCode = 'D' order by WrittenDate asc, substring(DelDocNum, 8, 4) asc

    open cursor_po

    fetch next from cursor_po into @InnerDelDocNum, @InnerItemCode,

    @InnerQtyDue, @InnerWrittenDate, @InnnerZoneCode, @InnerDeliveryDate

    while(@@fetch_status = 0)

    begin

    if(@NetWarehouse >= @InnerQtyDue)

    begin

    insert into #tempTableFinalAssigned values('N', getDate())

    set @NetWarehouse = (@NetWarehouse - @InnerQtyDue)

    end

    else

    begin

    set @PreAdviceID = (select top 1 isnull(PreAdviceID, '') from #tempTableRP where QtyDue >= @InnerQtyDue)

    if (@PreAdviceID is null or @PreAdviceID = '')

    begin

    set @PoCode = (select min(PO_Code) from #tempTablePO where QtyDue > 0)

    set @PoDate = (select min(ArrivalDate) from #tempTablePO where PO_Code = @PoCode)

    update #tempTablePO set QtyDue = (QtyDue - @InnerQtyDue) where PO_Code = @PoCode and ArrivalDate = @PoDate

    if(@DelDoc = @InnerDelDocNum)

    begin

    insert into #tempTableFinalAssigned values('T', @PoDate)

    end

    end

    else

    begin

    set @PoDate = (select min(DueDate) from #tempTableRP where PreAdviceID = @PreAdviceID and QtyDue > 0)

    update #tempTableRP set QtyDue = (QtyDue - @InnerQtyDue) where PreAdviceID = @PreAdviceID and DueDate = @PoDate

    if(@DelDoc = @InnerDelDocNum)

    begin

    insert into #tempTableFinalAssigned values('R', @PoDate)

    end

    end

    set @PreAdviceID = ''

    set @PoDate = ''

    end

    fetch next from cursor_po into @InnerDelDocNum, @InnerItemCode,

    @InnerQtyDue, @InnerWrittenDate, @InnnerZoneCode, @InnerDeliveryDate

    end

    close cursor_po

    deallocate cursor_po

    select case when DateType = 'T' then

    case when datepart(day, ReceiveDate) < 11 then 'Early ' + datename(month, ReceiveDate)

    when datepart(day, ReceiveDate) < 21 then 'Mid ' + datename(month, ReceiveDate)

    else 'Late ' + datename(month, ReceiveDate)

    end

    when DateType = 'R' then isnull(convert(varchar, ReceiveDate, 101), 'None')

    when DateType = 'N' then 'NAW'

    else 'None'

    end as ReceiveDate

    from #tempTableFinalAssigned

    return

  • The proc doesn't use the Return command to return a value. Thus, the variable is just being assigned the error code, which is probably 0 unless there's a problem with the proc.

    If you want to return a value other than the error code, you have to do so explicitly. For example:

    return 50

    or

    return @AnIntegerVariable

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'm sorry... then what would I do then if I wanted to return a varchar(30)?

  • I would like to return a varchar, but I am not sure how I would do that... I did not write the sproc.

  • There are two ways you can accomplish this.

    1) A stored proc will return the values of a select statement if it is the last statement in the procedure.

    2) You can use output parameters like this:

    create procedure ReturnVarchar

    (

    @RetVal varchar(30) output

    ) as begin

    select @RetVal = 'This is my output'

    end

    go

    declare @RetVal varchar(30)

    exec ReturnVarchar @RetVal output

    select @RetVal

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you very much and I apologize for my ignorance when it comes to this, but given my sproc that I have posted... if you look at the end in the case statement, I would like to return the ReceiveDate. How would I go about doing that given my sproc that I have posted.

    Again, thank you VERY much!

  • Notice the select statement is the last statement. It will return your select statement when you execute your proc. Look at the following:

    create procedure ReturnTest

    as

    select top 50 name into #Output from sys.objects

    select name from #Output

    return

    go

    This is a greatly simplified version of your proc but the basic elements are there. Now to have your proc return this list just execute it.

    exec ReturnTest

    Does that help?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • After reading your first post again it looks like you are trying to use the return values for an update. You can insert the data into a temp table so you could update using the temp table as a join. Look at the following for an example of getting the return into a temp table.

    create table #MyTable

    (

    name varchar(100)

    )

    insert into #MyTable

    exec ReturnTest

    select * from #MyTable

    You could use your temp table as a join condition in your update. Make sense?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you for your response, but I do not think I am doing this right... it is a little hard for me to understand right now given the context of my sproc (which I did not write, but understand for the most part) and the execution code, which I included first.

    When I try to select from the temp table in my execution code, I get an error message: Invalid object name '#tempTableFinalAssigned

  • The temp table doesn't exist in that context. It only exists inside the proc. If you want to select from it, you'll need to put the select statement inside the proc and then execute it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Then once again, I feel I am at an impasse of sorts... how can I get the date from the case statement at the end of my sproc?

    Here is the case statement:

    select case when DateType = 'T' then

    case when datepart(day, ReceiveDate) < 11 then 'Early ' + datename(month, ReceiveDate)

    when datepart(day, ReceiveDate) < 21 then 'Mid ' + datename(month, ReceiveDate)

    else 'Late ' + datename(month, ReceiveDate)

    end

    when DateType = 'R' then isnull(convert(varchar, ReceiveDate, 101), 'None')

    when DateType = 'N' then 'NAW'

    else 'None'

    end as ReceiveDate

    from #tempTableFinalAssigned

    I wants to use the date from here, the ReceiveDate to update a table from outside the sproc... how can I do this?

    Thank you... and I am sorry if I am not understanding this.

  • Can you modify the proc?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Are you wanting to get the results of that whole select? Look back at my example where I showed you how to execute a stored proc and get the result into a temp table. Start there and then you can write your update.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 22 total)

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