Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Wildcard for Integer MS SQL 2008

Hello Experts,

I'm trying to build a recordset query, that returns results based on a location selected.
The location value sent to the page that processes the query is an integer, for instance -

1 = Portsmouth
2 = Southampton
3 = London
4 - Birmingham
etc

This works fine if a variable ios sent, however the form can be submitted without a location being selected, in which case it should return results for all locations

If it were a string that was being sent, I could use the % wildcard, however I can't find a way of doing this.

I'm building the recordset using dreamweaver, which demands a default value.

My recordset currently looks like the code in the code section -

FYI 'MMColParam' references the variable && it is the line begining 'AND AD.JBALocation IN(' that i am strugling with

Any help you can offer will be gratefully appreciated -

Thank you

SELECT AD.JBAID, 
Lower(AD.JBATitle) AS JBATitle, 
AD.JBALocation, 
AD.JBACategory 
FROM dbo.JBAdvert AD 
WHERE JBASiteID = 30 

AND AD.JBALocation IN(Select JBLocation from JBLocation where JBLID = COALESCE('MMColParam', JBLID)) 

ORDER BY AD.JBAID DESC

Open in new window

Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

declare @mmcolparam varchar(10)
set @mmcolparam = 'MMColParam'  -- somehow 'MMColParam' turns into a variable right? it is passed in as string?
SELECT AD.JBAID,
Lower(AD.JBATitle) AS JBATitle,
AD.JBALocation,
AD.JBACategory
FROM dbo.JBAdvert AD
WHERE JBASiteID = 30
AND AD.JBALocation IN (Select JBLocation from JBLocation
      where (@mmcolparam = '') or
                 (@mmcolparam > '' and JBLID = cast(@MMColParam as integer)))
ORDER BY AD.JBAID DESC
Avatar of garethtnash

ASKER

Thanks CyberKiwi,
Kind of following your logic, - thank you...
However the query is slightly more complicated, I'm now trying to run it as a stored procedure - (full) code below -
However the SP below doesn't allow -
-----------
ORDER BY AD.JBAID DESC
-----------
I seem to be going around in circles with this one, perhaps you could advise best route?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[112]
@SiteID int,
@JBLID int,
@JBCID int,
@Keywords nvarchar(50)
AS
Select
AD.JBAID,          
Lower(AD.JBATitle) AS JBATitle,          
AD.JBALocation,          
AD.JBACategory,          
AD.JBAPayRate,          
CONVERT(CHAR(11),JBADatePosted,106) AS JBADatePosted,          
Lower(left(AD.JBADescription, 300) + '...') as JBADescription,  
AD.JBAFeaturedJob,  
AD.JBAOverWrite,  
CL.JBCLID,  
CL.JBCLName,          
CL.JBCLLogo,          
AV.ADViews,          
AP.Applications    
FROM dbo.JBAdvert AD          
left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AP.JBAPAdvertID = AD.JBAID          
left join (SELECT AV.AdvertID, AV.AdViews ADViews FROM dbo.JBADView AV GROUP BY AV.AdvertID, AV.AdViews) AV on AV.AdvertID = AD.JBAID          
inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID 
WHERE JBASiteID = @SiteID 
AND AD.JBALocation IN (Select JBLocation from JBLocation
      where (JBLID = COALESCE(@JBLID, JBLID))) 
AND AD.JBACategory IN(Select JBCategoryLabel from JBCategories 
where (JBCID = COALESCE(@JBCID, JBCID))) 
AND JBADescription LIKE '%'+ @Keywords + '%'

GO

Open in new window

This works, but takes 11/12 seconds to return the results -
see -
where (@JBLID = '0' OR JBLID = @JBLID))
where (@JBCID = '0' OR JBCID = @JBCID))
Help!!!
 

CREATE PROCEDURE [dbo].[1122]
@SiteID int,
@JBLID int,
@JBCID int,
@Keywords nvarchar(50)
AS
Select
AD.JBAID,          
Lower(AD.JBATitle) AS JBATitle,          
AD.JBALocation,          
AD.JBACategory,          
AD.JBAPayRate,          
CONVERT(CHAR(11),JBADatePosted,106) AS JBADatePosted,          
Lower(left(AD.JBADescription, 300) + '...') as JBADescription,  
AD.JBAFeaturedJob,  
AD.JBAOverWrite,  
CL.JBCLID,  
CL.JBCLName,          
CL.JBCLLogo,          
AV.ADViews,          
AP.Applications    
FROM dbo.JBAdvert AD          
left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AP.JBAPAdvertID = AD.JBAID          
left join (SELECT AV.AdvertID, AV.AdViews ADViews FROM dbo.JBADView AV GROUP BY AV.AdvertID, AV.AdViews) AV on AV.AdvertID = AD.JBAID          
inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID 
WHERE JBASiteID = @SiteID 
AND AD.JBALocation IN (Select JBLocation from JBLocation
      where (@JBLID = '0' OR JBLID = @JBLID)) 
AND AD.JBACategory IN(Select JBCategoryLabel from JBCategories 
where (@JBCID = '0' OR JBCID = @JBCID)) 
AND JBADescription LIKE '%'+ @Keywords + '%'
order by JBAID desc
GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect thank you