August 30, 2011 at 2:07 pm
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
August 30, 2011 at 2:12 pm
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
August 30, 2011 at 2:16 pm
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
August 31, 2011 at 9:02 am
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
August 31, 2011 at 11:21 am
I'm sorry... then what would I do then if I wanted to return a varchar(30)?
August 31, 2011 at 11:29 am
I would like to return a varchar, but I am not sure how I would do that... I did not write the sproc.
August 31, 2011 at 3:17 pm
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/
September 1, 2011 at 7:41 am
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!
September 1, 2011 at 7:54 am
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/
September 1, 2011 at 8:00 am
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/
September 1, 2011 at 9:40 am
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
September 1, 2011 at 9:52 am
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
September 1, 2011 at 11:08 am
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.
September 1, 2011 at 11:16 am
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
September 1, 2011 at 11:39 am
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