STEP 1 Create Procedure and run
Create Procedure [dbo].[ANILReports_New_Outstanding_POReport]
@PONumber Varchar(max)=null
as
begin
select po.PODate,
po.PONumber,
ven.VendorCode,
pod.Feet,
dbo.ItemDesc(pod.itemid) as description,
pod.Amount
--(pod.rate * pod.Amount) as Total
from Tran_PurchaseOrder as po
inner join Mast_Vendors as ven on po.vendorId=ven.VendorId
inner join Tran_PurchaseOrderdetails as pod on po.POId=pod.POId
where
@PONumber is Null or po.PONumber IN (SELECT * FROM fnSplit(@PONumber, ','))
End
EXEC ANILReports_New_Outstanding_POReport 'PO20110069,PO20110070'
2011-03-01 00:00:00.000 PO20110069 asd 654.00 123-1.5-0.145-10.24-B/X42-BEV-R1-.01-ERW-101p 15042.00
2011-03-01 00:00:00.000 PO20110069 asd 65.00 DR-13.375-0.373-0-L80HC-SMLS BEV-1-4.825-SMLS-CPV 2210.00
2011-03-01 00:00:00.000 PO20110069 asd 456.00 CP-10.75-0.290-18.99-J55-EUE-R2-4.767-ERW-CPV 5472.00
2011-03-17 00:00:00.000 PO20110070 123 1000.00 123-1.5-0.145-10.24-B/X42-BEV-R1-.01-ERW-101p 98000.00
2011-03-17 00:00:00.000 PO20110070 123 2000.00 CU-Select-0.362-0-Select-Select-1-6.125-SMLS-aq 110000.00
STEP 2
Create FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(max) -- List of delimited items
, @sDelimiter VARCHAR(max) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(max))
BEGIN
DECLARE @sItem VARCHAR(max)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
After next
