Solved

Store Procedure

Posted on 2012-03-26
4
255 Views
Last Modified: 2012-03-26
Guys, Does someone could me help with mu store procedure.

The problem is , always is returning a 999 means that the store procedure did not find the product. But when i run the select out of storeprocedure i can find the product.

Is is some thing wrong in this SP ?

Thanks guys


-------------------------
ALTER procedure [dbo].[CreatePickHelper]
(
       @Sku varchar,
       @Total int out
)       
as
declare @LocationsAvaliable int = 0;
declare @HoldOnStock int = 0;
declare @OrdersAlreadyCreated int = 0;
declare @ProductID int;
begin
          -- get the ProductId
          set @ProductID = isnull((select distinct id from product where upper(product.sku) = upper(@Sku)),0);
         
              -- get the QTD Locations Avaliable to pick
             
              if(@ProductID > 0)  
              begin
                    set @LocationsAvaliable = isnull(( select
                                                                        sum(qtd) as Total
                                                              from
                                                                        StockLocations as S
                                                              where locationID in ( select Id from locations where locationFlag_id in
                                                                                            ((select id from locationflag where ispickable = 1 )))
                                                                        and productId = @ProductID),0)
                   -- Get the QTD on pick on Hold                                                                    
                                            
                    set @HoldOnStock = isnull((select
                                                              sum(qtd)
                                                      from  pickingdetail
                                                      where pick_id in (select pick from HoldStock) and pickingdetail.product_id = @ProductID ),0)
              
                   -- Get the QTD on orders different of Canceled or Completed
              
                   set @OrdersAlreadyCreated = isnull(( select
                                                                          sum(qtd) - sum(qtd_picked) as TotalAlreadyCreated
                                                               from pickingdetail
                                                               where pick_id in (
                                                                                            select id from picking where order_id in (
                                                                                            select order_id from orderheader where OrderStatus not in(6,12)))
                                                                          ),0)
                                                                         
                 set @Total = @LocationsAvaliable - @HoldOnStock - @OrdersAlreadyCreated ;
               end  
               else      
                set @Total = 999;
end
0
Comment
Question by:Mortarello
  • 2
4 Comments
 

Author Comment

by:Mortarello
ID: 37765374
Is returning [0] on this line guyz

set @ProductID = isnull((select distinct id from product where upper(product.sku) = upper(@Sku)),0);

when I try this manually works.
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 37765480
Is your variable declared correctly?

  @Sku varchar,

This assumes that the SKU is one character long.

Try something like this to see if it works

  @Sku varchar(50)
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37765489
Try changing syntax of IsNull

set @ProductID = select distinct isnull(id,0) from product where upper(product.sku) = upper(@Sku);

Do same for other IsNull.....

HTH
Ashok
0
 

Author Comment

by:Mortarello
ID: 37765562
Hi guys,

Thanks for that. The sku should be varchar(40).

Tks man
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now