Monday, September 21, 2009

Stored Procedure with isnull function in SQL Server 2005

Create Procedure [dbo].[GetSupply]
as
select
i.ISBN as ISBN,
i.Title as Title,
i.Edition as Edition,
AvailableTitles=isnull(
(
select count(ISBN)
from Purchase
where ISBN=i.ISBN and Email=i.Email group by ISBN
),0 ),
FirstPurchDate= isnull
(
(
select cast(min(Purchase_date) as nvarchar(50))
from Purchase
where ISBN=i.ISBN group by ISBN
),
'Not Purchase Yet'
)
from INFO as i
where i.ISBN is not NULL
and i.ISBN <>''
and i.contacted ='W'
--and i.Title<>''
group by i.ISBN,i.Title,i.Edition,i.Email
order by i.ISBN

The AvailableTitles will show 0 and FirstPurchDate will show 'Not Purchase Yet' if query returns null for the both.

No comments:

Post a Comment