lets know what is COALESCE():it accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value. This tip describes two creative uses of the COALESCE() function in SQL Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [searchcandidate]
(@Cand_Fname varchar(100),
@Cand_Dob datetime,
@Skills_Id int,
@skills_other varchar(100)
)
as
begin
select c.Cand_Id,c.Cand_Fname,c.Cand_Dob ,c.Cand_Mob,s.Skills,c.Skill_other from can_infotbl c,Can_skilltbl s
where
c.Cand_Fname like coalesce(@Cand_Fname+'%',c.Cand_Fname) and
c.Cand_Dob = coalesce(@Cand_Dob ,c.Cand_Dob) and
c.Skills_Id = coalesce(@Skills_Id,c.Skills_Id) and
c.Skill_other like coalesce(@skills_other+'%',Skill_other)and
c.Skills_Id=s.Skills_Id
end
exec searchcandidate 'kumar',null,null,null
|
| Author: RajaSekaran R 03 Jun 2008 | Member Level: Gold Points : 0 |
Good Example...
|