August 30, 2006 at 3:31 am
I'm having trouble using the IN clause with a variable.
I would prefer not to use dynamic sql if I can avoid it.
below is a cut down version of what I need...
DECLARE @country AS VARCHAR(1000)
SET @country = 'UK,USA,France'
--If @country is passed and has a comma anywhere in the text add "'" around the values
IF isnull(@country,' ') <> ' ' AND charindex(',',@country) > 0
BEGIN
Declare @new_country AS VARCHAR(1000)
select @new_country = char(39)+replace(@country,',',char(39)+','+char(39))+char(39)
END
ELSE
BEGIN
SELECT @new_country = @country
END
select * from table where country in (isnull(@new_country,country))
The above select statement should look like this:
select * from table where country in ('UK','USA','France')
but I am getting 0 results.
Any ideas? Thanks
Darren
August 30, 2006 at 3:53 am
Darren
You need to use dynamic SQL for the last bit. Something like this (not tested):
DECLARE @sql
SET @sql = 'SELECT * FROM table WHERE country IN (' + ISNULL(@new_country, @country) + ')'
EXEC @sql
Better still, look up the syntax of sp_executesql.
You may also want to add some logic to check that @country is enclosed in quotation marks. This is because, for instance, if Spain is passed in (as opposed to 'Spain'), the select statement will fail.
John
August 30, 2006 at 4:04 am
The way I would approach this would be to have a user defined table function that splits out your country string and returns a table.
Your query would then become
SELECT * FROM table WHERE Country IN (SELECT StringValue FROM dbo.fnSplitString(@Country))
OR
SELECT table.*
FROM table
INNER JOIN dbo.fnSplitString(@Country) AS FN
ON table.Country = FN.StringValue
Without trying this I am not sure if this would actually achieve more than the dynamic sql
August 30, 2006 at 4:06 am
Thanks John,
I was hoping to avoid dynamic sql, but if there is no other way i will try that.
Thanks
Darren
August 30, 2006 at 4:10 am
David Thanks for that,
I have also tried that approach, but as I have a few list strings passed in, I would have to do lots of 'IF' statements to eliminate the joins if the value is not passed.
I was looking for a tidier method but I think dynamic sql or joins is the way I will have to do it.
Thanks
Darren
August 30, 2006 at 6:44 am
You NEVER HAVE TO use dynamic sql. There's always a way around it by coding correctly. Now I agree that sometimes it's a waste of time to go around dynamic sql but I really don't see the problem in this case. Can we see the rest of the code so that we can come up with a static solution?
August 30, 2006 at 8:04 am
Thanks for your response RGR'us.
I need to create a procedure to cover these search options on the application.
company_name, services_offered, adviser_type, country
any one, or all of these may be passed to the database.
I also need to be able to return the data in pages.
This is the code I have,
DECLARE @company_name AS VARCHAR(110)
DECLARE @services_offered AS VARCHAR(5000)
DECLARE @adviser_type AS VARCHAR(1000)
DECLARE @country AS VARCHAR(1000)
DECLARE @search_option AS BIT --company_name starts_with/contains
DECLARE @start_record AS INT --start record for paging
DECLARE @records_returned AS INT --number of records returned on a page
SET @company_name = 'AP'
SET @services_offered = NULL
SET @adviser_type = NULL
SET @country = NULL
SET @search_option = 0
SET @start_record = 0
SET @records_returned = 20
--***************************Variable checks***************************--
--check to see if the user has selected 'starts with' or 'contains'
IF @Search_Option = 1
set @company_name = '%'+@company_name+'%'
ELSE
set @company_name = @company_name+'%'
--If @country is passed add "'" around the values
IF isnull(@country,' ') <> ' ' AND charindex(',',@country) > 0
BEGIN
Declare @new_country AS VARCHAR(1000)
select @new_country = char(39)+replace(@country,',',char(39)+','+char(39))+char(39)
END
ELSE
BEGIN
SELECT @new_country = @country
END
--If @country is passed add "'" around the values
IF isnull(@adviser_type,' ') <> ' ' AND charindex(',',@adviser_type) > 0
BEGIN
Declare @new_adviser_type AS VARCHAR(1000)
select @new_adviser_type = char(39)+replace(@adviser_type,',',char(39)+','+char(39))+char(39)
END
ELSE
BEGIN
SELECT @new_adviser_type = @adviser_type
END
--***************************Variable checks end***********************--
--Create a temporary table to hold paging i.d's
CREATE TABLE #paging
(
paging_id INT IDENTITY(1,1)
,company_id INT
,company_name VARCHAR(110) --company name added to do an order by with a select distinct
)
--Populate Searching table
INSERT INTO #paging (company_id, company_name)
SELECT DISTINCT
pbas.company_id
,pbas.company_name
FROM pfo_both_adviser_search pbas
LEFT JOIN pfo_both_adviser_services_offered pbaso
ON pbas.company_id = pbaso.company_id
WHERE pbas.company_name like ISNULL(@company_name,pbas.company_name)
AND isnull(pbaso.services_offered,'') like isnull('%'+@services_offered+'%',isnull(pbaso.services_offered,''))
AND pbas.adviser_type in (isnull(@new_adviser_type,pbas.adviser_type))
AND pbas.country in (isnull(@new_country,pbas.country))
ORDER BY pbas.company_name
--Perform Search with paging
SELECT pbas.company_id
,pbas.company_name
,pbaso.services_offered
FROM #paging p
JOIN pfo_both_adviser_search pbas
ON p.company_id = pbas.company_id
JOIN pfo_both_adviser_services_offered pbaso
ON p.company_id = pbaso.company_id
WHERE p.paging_id > @start_record
AND p.paging_id <= @start_record + @records_returned
Drop table #paging
August 30, 2006 at 8:23 am
I don't see a lot of possible improvements. How fast is this running on your server? Is it at acceptable speed?
Those types of searches are pretty much impossible to do efficiently (with the ever incrementing search criterias) unless you are ready to do a lot of coding/tuning.
The only thing I notice is the left join... that's already likely to force 1 table scan (assuming no big usefull filter exists there). I don't really see hope of ever tuning this query to a speeding demon. Maybe somebody will see something else you can do.
August 30, 2006 at 8:35 am
Thanks,
Speed is not an issue.
The problem I have is when I run the query these line's do not work properly
AND pbas.adviser_type in (isnull(@new_adviser_type,pbas.adviser_type))
AND pbas.country in (isnull(@new_country,pbas.country))
the two variables @new_adviser_type & @new_country will either be a string of values e.g. 'UK','USA','Ireland','France' or NULL
do you know another way of doing this?
Thanks darren
August 30, 2006 at 9:31 am
Run this to create my set based split function :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber < LEN(@vcDelimiter + @IDs + @vcDelimiter)
) dtSplitted where len(dtSplitted.EachID) > 0
GO
Once this is run, you can incorporate it like this in your query :
AND (pbas.adviser_type in (Select EachID from dbo.fnSplit_Set (@new_adviser_type, ',') OR @new_adviser_type IS NULL)
AND (pbas.country in (Select EachID from dbo.fnSplit_Set ( @new_country , ',') OR @new_country IS NULL)
August 30, 2006 at 9:35 am
Darren
That's because you haven't addressed the original problem of needing dynamic SQL or a table-value function. Although I suggested a way of doing the former, it doesn't scale up very well to your overall query - it would look ugly and be difficult to read. And, of course, it would open you up to a SQL injection attack. David's solution seems much neater. If you're worried about the join, then use his first option.
John
August 31, 2006 at 10:42 am
Can also use pattern matching using a delimeter
i.e.
declare @x varchar(255)
set @x = '|a|b|c|' --this is the delimeted list youd pass into the stored procedure
select * from mytable where '%|' + searchfield + '|%' like @x
can use in lookups:
select * from mylargetable where
mylookupid in (select * from mylookuptable where '%|' + myvar + '|%' like @x)
I think this code should run as quick as the above code
Phil Nicholas
August 31, 2006 at 10:53 am
Just a small not about the use of like '%%' to search a in list... if you have very few items in the list, the code will run pretty quickly, but since the whole string must be checked for every row, this can quickly turn into a snail race. My set based version may seem more complexe but will often outperform any other methods (not to say always).
September 1, 2006 at 4:03 am
Thanks for all your excellent advice!
I will consider all options and see which works best in my situation.
Thanks
Darren
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply