Stored Procedure params

MCnKC · 05-24-2001, 09:44 AM

In the following stored procedure, I am passing a value either 1 or 0 (true/false) to either include records with zero quantities or exclude zero quantities depending on which argument is supplied. However, the stored proc will not return any records when there is data that meets the query. Where is the fault in my query. Any help is greatly appreciated!!

CREATE proc all_wildfindinventory

-- pass max rows to return, logiview customer id, product category and searchstring
@rows int,
@customer nvarchar(50),
@category nvarchar(15),
@searchstring nvarchar(50),
@searchby nvarchar(50) = null,
@sizepack nvarchar(50) = null,
@warehouse nvarchar(50) = '%',
@nonzero tinyint = null

as

-- set max row to return to @rows
set rowcount @rows

declare @product nvarchar(50),
@desc nvarchar(50),
@batch nvarchar(50),
@lot nvarchar(50),
@size nvarchar(50),
@pickkey nvarchar(50)

begin

if coalesce(@searchby, 'product&#39

= 'product'
begin
select @product = @searchstring
end
else
begin
select @product = '~!@#$%'
end

if coalesce(@searchby, 'desc&#39

= 'desc'
begin
select @desc = @searchstring
end
else
begin
select @desc = '~!@#$%'
end

if coalesce(@searchby, 'batch&#39

= 'batch'
begin
select @batch = @searchstring
end
else
begin
select @batch = '~!@#$%'
end

if coalesce(@searchby, 'lot&#39

= 'lot'
begin
select @lot = @searchstring
end
else
begin
select @lot = '~!@#$%'
end

if coalesce(@searchby, 'pickkey&#39

= 'pickkey'
begin
select @pickkey = @searchstring
end
else
begin
select @pickkey = '~!@#$%'
end

if coalesce(@searchby, 'sizepack&#39

= 'sizepack'
begin
select @size = @searchstring
end
else
begin
select @size = '~!@#$%'
end

if coalesce(@category, '&#39

= ''
begin
select @category = '%'
end

if coalesce(@sizepack, '&#39

= ''
begin
select @sizepack = '%'
end

//here begins the select depending on which argument is passed (1 or 0)
if coalesce(@nonzero, '0&#39

= 0
begin
select tlnproducts.product,
coalesce(max(tlnproducts.department), 'N/A&#39

as category,
max(tlnproducts.description) as description, max(tlnproducts.alternate) as alternate,
coalesce(sum(tlninventory.onhand), 0) as onhand,
coalesce(sum(tlninventory.available), 0) - coalesce(sum(distinct reserved), 0) as available,
coalesce(sum(distinct tlninventory.reserved), 0) as reserved,
coalesce(sum(tlninventory.duein), 0) as duein,
coalesce(sum(tlninventory.onhold), 0) as onhold,
coalesce(sum(tlninventory.damaged), 0) as damaged,
coalesce(sum(tlninventory.pending), 0) + coalesce(sum(distinct reserved), 0) as pending,
max(tlnproducts.sizepack) as sizepack,
pickpreference =
case max(tlnproducts.pickpreference)
when 'b' then 'Batch'
when 'c' then 'Lot'
when 'l' then 'LIFO'
when 'p' then 'FIFO'
else max(tlnproducts.pickpreference)
end,
coalesce(cast(max(tlnproducts.reorderpoint) as nvarchar), 'None&#39

as reorderpoint
from tlnproducts
left outer join tlninventory on
tlnproducts.customer = tlninventory.customer and
tlnproducts.warehouse = tlninventory.warehouse and
tlnproducts.product = tlninventory.product
left outer join tlncategory on
tlnproducts.customer = tlncategory.customer and
tlnproducts.warehouse = tlncategory.warehouse and
tlnproducts.department = tlncategory.department
where (tlnproducts.product in (
select distinct tlnproducts.product
from tlnproducts
left outer join tlninventory on
tlnproducts.customer = tlninventory.customer and
tlnproducts.warehouse = tlninventory.warehouse and
tlnproducts.product = tlninventory.product
where(tlnproducts.warehouse like @warehouse and tlnproducts.customer = @customer
and coalesce(tlnproducts.department, '&#39

like @category and

coalesce(tlnproducts.sizepack, '&#39

like @sizepack) and
(coalesce(tlnproducts.product, '&#39

like '%' + coalesce(@product, '&#39

+ '%' or
coalesce(tlnproducts.description, '&#39

like '%' + coalesce(@desc, '&#39

+ '%' or
coalesce(tlnproducts.sizepack, '&#39

like '%' + coalesce(@size, '&#39

+ '%' or
coalesce(tlninventory.batch, '&#39

like '%' + coalesce(@batch, '&#39

+ '%' or
coalesce(tlninventory.customerlot, '&#39

like '%' + coalesce(@lot, '&#39

+ '%' or
coalesce(tlninventory.pickkey, '&#39

like '%' + coalesce(@pickkey, '&#39

+ '%&#39

)) and
tlnproducts.warehouse like @warehouse and tlnproducts.customer = @customer
group by tlnproducts.product
order by tlnproducts.product
end

else

begin

select tlnproducts.product,
coalesce(max(tlnproducts.department), 'N/A&#39

as category,
max(tlnproducts.description) as description, max(tlnproducts.alternate) as alternate,
coalesce(sum(tlninventory.onhand), 0) as onhand,
coalesce(sum(tlninventory.available), 0) - coalesce(sum(distinct reserved), 0) as available,
coalesce(sum(distinct tlninventory.reserved), 0) as reserved,
coalesce(sum(tlninventory.duein), 0) as duein,
coalesce(sum(tlninventory.onhold), 0) as onhold,
coalesce(sum(tlninventory.damaged), 0) as damaged,
coalesce(sum(tlninventory.pending), 0) + coalesce(sum(distinct reserved), 0) as pending,
max(tlnproducts.sizepack) as sizepack,
pickpreference =
case max(tlnproducts.pickpreference)
when 'b' then 'Batch'
when 'c' then 'Lot'
when 'l' then 'LIFO'
when 'p' then 'FIFO'
else max(tlnproducts.pickpreference)
end,
coalesce(cast(max(tlnproducts.reorderpoint) as nvarchar), 'None&#39

as reorderpoint
from tlnproducts
left outer join tlninventory on
tlnproducts.customer = tlninventory.customer and
tlnproducts.warehouse = tlninventory.warehouse and
tlnproducts.product = tlninventory.product
left outer join tlncategory on
tlnproducts.customer = tlncategory.customer and
tlnproducts.warehouse = tlncategory.warehouse and
tlnproducts.department = tlncategory.department
where (tlnproducts.warehouse like @warehouse and tlnproducts.customer = @customer
and coalesce(tlnproducts.department, '&#39

like @category and
coalesce(tlnproducts.sizepack, '&#39