--Create temporary table
Create table #PurchReceive
(
 Packingslipid nvarchar(30), deliverydate datetime, itemid nvarchar(35),
 name nvarchar(1000), ordered numeric(28,12), Qty numeric(28,12), remain numeric(28,12),
 dimension nvarchar(10), purchid nvarchar(30), internalpackingslipid nvarchar(30),
 inventdimid nvarchar(30), dataareaid nvarchar(4)
 )
 
--pull out all records satisfied by issued critera and append to temporary table
Insert into #PurchReceive(Packingslipid,deliverydate,itemid,name,ordered,Qty,remain,
dimension,purchid,internalpackingslipid,inventdimid,dataareaid)
Select 
	 v.PACKINGSLIPID
	,v.DELIVERYDATE
	,v.ITEMID
	,v.NAME
	,v.ORDERED
	,v.QTY
	,v.REMAIN
	,v.DIMENSION
	,v.PURCHID
	,v.INTERNALPACKINGSLIPID
	,v.INVENTDIMID
	,v.DATAAREAID
From VENDPACKINGSLIPTRANS v
Where v.DATAAREAID =(@Dataareaid)
and v.DELIVERYDATE >=(@Startdate) and v.DELIVERYDATE <(@Enddate)


--Join from other table for data manipulation and relatte other fields.
Select p.PACKINGSLIPID
	,p.DELIVERYDATE
	,p.ITEMID
	,p.NAME
	,p.ORDERED
	,p.QTY
	,p.REMAIN
	,p.DIMENSION
	,p.PURCHID
	,p.INTERNALPACKINGSLIPID
	,p.INVENTDIMID
	,p.DATAAREAID
From  #PurchReceive p
Inner Join PURCHLINE l
On l.ITEMID = p.itemid COLLATE Chinese_Taiwan_Stroke_CI_AS
	and l.PURCHID = p.purchid COLLATE Chinese_Taiwan_Stroke_CI_AS
Left Outer Join PURCHTABLE u
On p.PURCHID = u.PURCHID COLLATE Chinese_Taiwan_Stroke_CI_AS
Left Outer Join INVENTDIM i
On p.INVENTDIMID = i.INVENTDIMID COLLATE Chinese_Taiwan_Stroke_CI_AS
and i.DATAAREAID = (@Dataareaid)
Left Outer Join INVENTTABLE t
	On t.ITEMID = p.ITEMID COLLATE nese_Taiwan_Stroke_CI_AS
order by p.purchid


Drop table #PurchReceive

quote:

---

Originally posted by Villanuev

Guys,

How would i place an error message indicate no records found based on the criteria in SSRS report. thanks.

---