Hi Friends,
how I can return table from sqlserver stored procedure as out parameter along with another out parameter. please find below my stored procedure . or if possible how I can get multipul value from @Erroritem nvarchar(20) out, @errorqty int
out
thank you
GO
ALTER PROCEDURE [dbo].[Quotation_to_invoice]
(
@TableVar dbo.inventory_table READONLY, @mstoreid int, @mcityid int,@mregionid int, @order_type nvarchar(20),@terms nvarchar(20),@invoice_sequence nvarchar(20) out,@quotation_no nvarchar(20)
, @Erroritem nvarchar(20) out, @errorqty int out
)
AS
BEGIN
DECLARE @SNO int ;
DECLARE @FR nvarchar(40);
DECLARE @PartNO nvarchar(60);
DECLARE @itemName nvarchar(100);
DECLARE @Brand nvarchar(60);
DECLARE @Origion nvarchar(60);
DECLARE @Location nvarchar(60);
DECLARE @Qty nvarchar(10);
DECLARE @Price nvarchar(10);
DECLARE @Discount nvarchar(10);
DECLARE @Total nvarchar(20);
Declare @mseq int;
Declare @tbalance int;
Declare @mainbalance int;
declare @tbalance_counter int;
DECLARE @errortab table (errorpartno nvarchar(20), qty int)
SET NOCOUNT ON;
DECLARE mycursor CURSOR FAST_FORWARD
FOR SELECT sno,fr,partno,itemname,brand,origion,location,qty,price,discount,total FROM @TableVar
select @invoice_sequence= NEXT VALUE FOR invoice_Sequence
SET @tbalance_counter=0
OPEN mycursor FETCH NEXT FROM myCursor INTO @SNO, @FR, @PartNO, @itemName, @Brand, @Origion, @Location, @Qty , @Price, @Discount , @Total ;
WHILE (@@FETCH_STATUS = 0 )
BEGIN
if @order_type='Quotation' and @terms ='Cash'
begin
select @mainbalance=sum(QTY-USED_balance) from inv_balance
where part_number=@partno and STOREID=@mstoreid and CITYID=@mcityid
and REGIONID=@mregionid and (qty-USED_balance) >0
IF @qty > @mainbalance
Begin
set @Erroritem =@partno
set @errorqty=@mainbalance
insert into @errortab values(@partno,@mainbalance )
end
ELSE
begin
IF (@mainbalance >= @qty and @mainbalance is not null)
Begin
--insert into quotation2(qty,itemname) values(@mainbalance,@itemname)
WHILE (@tbalance_counter < =@QTY and @mainbalance >= @qty)
begin
insert into quotation2(qty,itemname) values(@mainbalance,@itemname)
select @mseq = min(seqno) from inv_balance
where part_number=@PartNO and STOREID=@mstoreid
and CITYID=@mcityid and REGIONID=@mregionid and (qty-USED_balance) >0
select @tbalance =(qty-USED_balance) from inv_balance
where part_number=@PartNO and STOREID=@mstoreid
and CITYID=@mcityid and REGIONID=@mregionid and seqno=@mseq
if ( @tbalance>=@Qty OR @tbalance<=@Qty)
Begin
-- insert into quotation2(itemname) values('100')
update inv_balance set USED_BALANCE = USED_BALANCE + @QTY
where part_number=@PartNO and STOREID=@mstoreid
and CITYID=@mcityid and REGIONID=@mregionid and seqno=@mseq
set @tbalance_counter = @tbalance_counter + @tbalance
end
end
end
End
---- mainbalance
insert into inv_trx(TRX_TYPE,TRX_DATE,PART_NUMBER,ItemName,brand, origion,location,
STOREID,DISCOUNT_AMOUNT, CITYID,REGIONID,QTY,SELLING_PRICE,COST_PRICE,REF_NO,BATCHNO,invoice_no,FR,Quotation_no)
VALUES('S',getdate(),@partno,@itemName,@Brand,@origion,@location,
@mstoreid, @Discount, @mcityid ,@mregionid,
@qty, @price,@price, NULL,null ,@invoice_sequence,@FR,@quotation_no)
end ---@mainbalance
FETCH NEXT FROM myCursor INTO
@SNO, @FR, @PartNO, @itemName, @Brand, @Origion, @Location, @Qty , @Price, @Discount , @Total ;
end
----main while loop
CLOSE myCursor ;
DEALLOCATE myCursor;
--update quotation set Acknowledge_flag='Y' where Quotation_no=@quotation_no
end
asad
