garethtnash
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
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
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?
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
ASKER
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!!!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect thank you
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