Solved

Wildcard for Integer MS SQL 2008

Posted on 2010-08-25
5
896 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:garethtnash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33518658
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
0
 

Author Comment

by:garethtnash
ID: 33519050
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

0
 

Author Comment

by:garethtnash
ID: 33519137
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

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33519371
SQL Server normally produce generalized plans that tries to work best in all situations.
You probably innately know that when @JBLID is non-zero, it will be a lot faster to start the multi-table query from JBAdvert filtered by JBALocation, but SQL Server needs the plan (which it will reuse) to work well "in most/average cases".
Bearing that in mind, here is one way to produce different plans from the same SP using dynamic SQL.
CREATE PROCEDURE [dbo].[1122]
@SiteID int,
@JBLID int,
@JBCID int,
@Keywords nvarchar(50)
AS
declare @nsql nvarchar(max);
set @nsql = '
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 ' +
	case when @JBLID = '0' then '' else
	'where (JBLID = ' + str(@JBLID) + ')' end +
')
AND AD.JBACategory IN(Select JBCategoryLabel from JBCategories ' +
	case when @JBCID = '0' then '' else
	'where (JBCID = ' + str(@JBCID) + ')' end +
') ' +
	case when @Keywords = '' then '' else
	'AND JBADescription LIKE ''%''+ @Keywords + ''%'' ' end +
'order by JBAID desc';

exec sp_executeSQL @nsql, N'@Keywords nvarchar(50)', @Keywords
GO

Open in new window

0
 

Author Closing Comment

by:garethtnash
ID: 33529009
Perfect thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question