Your first thought might be that you need two stored procedures. Nope, you can do it with one.
The following code snippet is part of a larger stored procedure, but is enough to explain how to perform a “one or all” type of query. The item number (@itemNumb) and location code (@locncode) are passed in as parameters.
To select only 1 item, use that item number and location code as the parameters to the stored procedure. The WHERE clause will return only those rows whose SOPTYPE is 2, ITEMNMBR matches the itemNumb parameter and LOCNCODE matches the locncode parameter.
To select all items, use ‘ALL’ as the value for the itemNumb and locncode parameters. Since the values for these parameters are ‘ALL’, each of the tests will pass for all item numbers and location codes.
Note that this is not the complete stored procedure, just the portion that will do the initial selection of one or all items.
@itemNumb AS VARCHAR(31),
@locncode AS VARCHAR(11)
SELECT DISTINCT L.ITEMNMBR, L.LOCNCODE
FROM SOP10200 L
WHERE L.SOPTYPE = 2
AND (L.ITEMNMBR = @itemNumb OR @itemNumb = 'ALL')
AND (L.LOCNCODE = @locncode OR @locncode = 'ALL')