>SELECT r.*, >> p.PartNo, >> p.Description, >> u.FullName, >> w.Caption AS Warehouse >> FROM Returns r >> JOIN Parts p ON p.PartId = r.PartId >> JOIN Warehouses w ON w.WarehouseId = r.WarehouseId >> JOIN Users u ON u.UserId = r.UserId >> WHERE r.WarehouseId = @WarehouseId AND >> (@Status IS NULL OR r.Status = @Status) >> ORDER BY p.PartNo, r.SerialNo DESC > >>
>>ReturnId WarehouseId SerialNo Details Status ActionDate >>----------- ----------- ----------- ------------- ------ ----------------------- >>1 20 NULL Details 1 NULL 2009-06-17 09:01:44.240 >>2 20 NULL Carlsbad NULL 2009-06-17 09:08:10.533 >>>>
>> >>CREATE PROCEDURE ap_GetReturnsForWarehouse >> @WarehouseId INT, >> @Status TINYINT = NULL >> >>AS >>BEGIN >> >> SELECT r.*, >> p.PartNo, >> p.Description, >> u.FullName, >> w.Caption AS Warehouse >> FROM Returns r >> JOIN Parts p ON p.PartId = r.PartId >> JOIN Warehouses w ON w.WarehouseId = r.WarehouseId >> JOIN Users u ON u.UserId = r.UserId >> WHERE r.WarehouseId = @WarehouseId AND >> r.Status = ISNULL(@Status, r.Status) >> ORDER BY p.PartNo, r.SerialNo DESC >> >>END >> >>>>
>>exec ap_GetReturnsForWarehouse @WarehouseId = 20, @Status = NULL >>>>