Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Help in T-SQL

Hi I have the following case.i would like a precise and efficient SQL Statement for it.

I have 7 parameters which are used for comparison. I need to run the query with these parameters. which ever is not null should be compared and the null ones should be ignored. AS a result I end with lots of "if' statements as the parameters form a lot of copmbination. Is there any way to cut short the code

ALTERPROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]

@sSkillvarchar(50),

@iCountryIDint=NULL,

@iStateIDint=NULL,

@iJobTypeIDint=NULL,

@iMinSalint=NULL,

@iMaxSalintNULL,

@iPeriodPostedintNULL

AS

IF @iCountryID!=NULLAND @iStateID=NULLAND @iJobTypeID=NULLand @iMinSal=NULLand @iMaxSal=Nulland iPeriodPosted=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDand

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC

IF @iCountryID!=NULLAND @iStateID!=NULLAND @iJobTypeID=NULLand @iMinSal=NULLand @iMaxSal=Nulland iPeriodPosted=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDAND

targetstateid=@iStateIDand

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC

IF @iCountryID!=NULLAND @iStateID!=NULLAND @iJobTypeID!=NULLand @iMinSal=NULLand @iMaxSal=Nulland iPeriodPosted=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDAND

targetstateid=@iStateIDand

jobtypeid=@iJobTypeIDand

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC

IF @iCountryID!=NULLAND @iStateID!=NULLAND @iJobTypeID!=NULLand @iMinSal!=NULLand @iMaxSal=Nulland iPeriodPosted=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDAND

targetstateid=@iStateIDand

jobtypeid=@iJobTypeIDand

minsalary>= @iminsaland

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC

IF @iCountryID!=NULLAND @iStateID!=NULLAND @iJobTypeID!=NULLand @iMinSal!=NULLand @iMaxSal!=Nulland iPeriodPosted=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDAND

targetstateid=@iStateIDand

jobtypeid=@iJobTypeIDand

minsalary>= @iminsaland

maxsalary>= @imaxsaland

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC

IF @iCountryID!=NULLAND @iStateID!=NULLAND @iJobTypeID!=NULLand @iMinSal!=NULLand @iMaxSal!=Nulland iPeriodPosted=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDAND

targetstateid=@iStateIDand

jobtypeid=@iJobTypeIDand

minsalary>= @iminsaland

maxsalary>= @imaxsaland

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC

IF @iCountryID!=NULLAND @iStateID!=NULLAND @iJobTypeID!=NULLand @iMinSal!=NULLand @iMaxSal!=Nulland iPeriodPosted!=NULL

SELECT*FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@iCountryIDAND

targetstateid=@iStateIDand

jobtypeid=@iJobTypeIDand

minsalary>= @iminsaland

maxsalary>= @imaxsaland

resumetextlike('%'+ @sSkill+'%')

ORDERBY [postdate]DESC
[18708 byte] By [UmaRamiya] at [2008-1-5]
«« Project server 2003
»» Broken link testing - Visual studio
# 1
hi, try this

ALTER PROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]
@sSkill varchar(50),
@iCountryID int=NULL,
@iStateID int=NULL,
@iJobTypeID int=NULL,
@iMinSal int=NULL,
@iMaxSal int NULL,
@iPeriodPosted int NULL
AS

SELECT *
FROM [dbo].[JobsDb_Resumes]
where targetcountryid=coalesce(@iCountryID,targetcountryid) AND
targetstateid=coalesce(@iStateID,targetstateid) and
jobtypeid=coalesce(@iJobTypeID,jobtypeid) and
minsalary >= coalesce(@iminsal,minsalary) and
maxsalary >= coalesce(@imaxsal,maxsalary) and
resumetext like ('%' + @sSkill + '%')
ORDER BY [postdate] DESC

[rh4m1ll3]RhamilleGolimlim at 2007-9-28 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2
Hi,

Look at the following and let me know whether this helped or not.

SELECT
*
FROM
[dbo].[JobsDb_Resumes]
WHERE
(CASE WHEN @iCountryID IS NULL THEN targetcountryid ELSE @iCountryID END) = targetcountryid
AND (CASE WHEN @iStateID IS NULL THEN targetstateid ELSE @iStateID END) = targetstateid
AND (CASE WHEN @iJobTypeID IS NULL THEN jobtypeid ELSE @iJobTypeID END) = jobtypeid
AND resumetext LIKE (CASE WHEN @sSkill IS NULL THEN '%%' ELSE '%' + @sSkill + '%' END)
AND minsalary >= (CASE WHEN @iminsal IS NULL THEN 0 ELSE @iminsal END)
AND maxsalary >= (CASE WHEN @imaxsal IS NULL THEN 0 ELSE @imaxsal END)
ORDER BY
[postdate] DESC

ziqbalbh at 2007-9-28 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

hi,

Try this, this is the most efficient way. I hope, i did not forget any of your parameter.

Code Snippet

SELECT

*

FROM

[dbo].[JobsDb_Resumes]

WHERE

(targetcountryid = @iCountryID OR @iCountryID IS NULL)

AND

(resumetext LIKE ('%' + @sSkill + '%') OR @sSkill IS NULL)

AND

(targetstateid = @iStateID OR @iStateID IS NULL)

AND

(jobtypeid = @iJobTypeID OR @iJobTypeID IS NULL)

AND

(minsalary >= @iminsal OR @iminsal IS NULL)

AND

(maxsalary >= @imaxsal OR @imaxsal IS NULL)

ORDER BY [postdate] DESC

Regards,

Janos

JanosBerke at 2007-9-28 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4
Thanksyou my friend. It worked Smile
UmaRamiya at 2007-9-28 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified