thayduck
asked on
Where h.category like '%' + @pcategoryo + '%' RETURNS NOTHING
SQL2008 R2
Cannot seem to get this Where clause to work in my sql stored procedure. Returns nothing.
where h.category like '%' + @pcategoryo + '%'
@pcategoryo contains BUSGR,BUSGY,BUSGT
@pcategoryo is a SSRS report parm
h.category = BUSGR
Cannot seem to get this Where clause to work in my sql stored procedure. Returns nothing.
where h.category like '%' + @pcategoryo + '%'
@pcategoryo contains BUSGR,BUSGY,BUSGT
@pcategoryo is a SSRS report parm
h.category = BUSGR
ASKER
I do use the table valued split function now, but do not understand why this would not work.
Your query is evaluating to
I would assume that nothing in your table has a category value containing that string.
WHERE h.category LIKE '%BUSGR,BUSGY,BUSGT%'
I would assume that nothing in your table has a category value containing that string.
ASKER
I just assumed that when you surround BUSGR,BUSGY,BUSGT with %, it meant that I should have gotten a match on BUSGR since BUSGR is in that string somewhere.
%
Any string of zero or more characters.
WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
So would not BUSGR,BUSGY,BUSGT be like the book title and I am looking for the word BUSGR.
Just confusing....
%
Any string of zero or more characters.
WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
So would not BUSGR,BUSGY,BUSGT be like the book title and I am looking for the word BUSGR.
Just confusing....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's actually pretty simple you are just assuming functionality that doesn't exist. How would the LIKE operator know to interpret your string as a delimited list?
What you might be looking for is the IN operator but you would need to use dynamic SQL to make it work and that is generally not the best solution. The LIKE operator is used to find strings that appear in other strings. If the parameter is a list of valid category values then you should not be using the LIKE operator at all. Instead I would recommend using the split function as i mentioned earlier with a slight modification:
For the sake of completeness...
What you might be looking for is the IN operator but you would need to use dynamic SQL to make it work and that is generally not the best solution. The LIKE operator is used to find strings that appear in other strings. If the parameter is a list of valid category values then you should not be using the LIKE operator at all. Instead I would recommend using the split function as i mentioned earlier with a slight modification:
SELECT ...
FROM myTable AS h
INNER JOIN dbo.Split(@pcategoryo, ',') AS category
ON h.category = category.value
For the sake of completeness...
CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO
For example:
DECLARE @pcategoryo varchar(100)
SET @pcategoryo = 'BUSGR,BUSGY,BUSGT'
SELECT *
FROM (
SELECT 1 AS row#, 'BUSGR' AS category UNION ALL
SELECT 2, 'BUSZZ' UNION ALL
SELECT 3, 'BUSGT'
) AS h
WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'
Btw, that's an extremely inefficient split. I wouldn't suggest using it for large data sets.
DECLARE @pcategoryo varchar(100)
SET @pcategoryo = 'BUSGR,BUSGY,BUSGT'
SELECT *
FROM (
SELECT 1 AS row#, 'BUSGR' AS category UNION ALL
SELECT 2, 'BUSZZ' UNION ALL
SELECT 3, 'BUSGT'
) AS h
WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'
Btw, that's an extremely inefficient split. I wouldn't suggest using it for large data sets.
ASKER
Scott, your first suggestion worked just fine with a small change.
It does exactly what I want it to do now.
I take the report parm as is BUSYE,BUSGR,BUSOK and using the like (%,%)
it includes the records if h.category = any one of the parms chosen by the user in the report parms.
WHERE @pcategoryo LIKE '%' +h.category+%'
Now I do not have to create or read any temp tables.
Do you guys see any issues in using this code this way instead of creating temp tables ?
I will test some more but it looks good to me...
It does exactly what I want it to do now.
I take the report parm as is BUSYE,BUSGR,BUSOK and using the like (%,%)
it includes the records if h.category = any one of the parms chosen by the user in the report parms.
WHERE @pcategoryo LIKE '%' +h.category+%'
Now I do not have to create or read any temp tables.
Do you guys see any issues in using this code this way instead of creating temp tables ?
I will test some more but it looks good to me...
ASKER
This works fine for report type parms that contain a fixed length code.
I just wanted another way to compare report parms without having to create a temp table and splitting of the parm selections into the table then reading the table in a Where clause.
Thanks Scott and Brian.
I just wanted another way to compare report parms without having to create a temp table and splitting of the parm selections into the table then reading the table in a Where clause.
Thanks Scott and Brian.
>> This works fine for report type parms that contain a fixed length code. <<
I added the delimiter, in this case a comma, to the comparisons allow even variable-length values to be accurately tested.
For example:
DECLARE @pcategoryo varchar(100)
SET @pcategoryo = 'BUSGR,BUSGY,BUSGT,BUSS'
SELECT *
FROM (
SELECT 1 AS row#, 'BUSGR' AS category UNION ALL
SELECT 2, 'BUSZZ' UNION ALL
SELECT 3, 'BUSG' UNION ALL
SELECT 4, 'BUS' UNION ALL
SELECT 5, 'BUSS' UNION ALL
SELECT 6, 'BUSSY'
) AS h
WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'
I added the delimiter, in this case a comma, to the comparisons allow even variable-length values to be accurately tested.
For example:
DECLARE @pcategoryo varchar(100)
SET @pcategoryo = 'BUSGR,BUSGY,BUSGT,BUSS'
SELECT *
FROM (
SELECT 1 AS row#, 'BUSGR' AS category UNION ALL
SELECT 2, 'BUSZZ' UNION ALL
SELECT 3, 'BUSG' UNION ALL
SELECT 4, 'BUS' UNION ALL
SELECT 5, 'BUSS' UNION ALL
SELECT 6, 'BUSSY'
) AS h
WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'
ASKER
How did I miss that.
Your original code does handle both fixed or variable length parameter codes.
Thanks for pointing that out to me.
Your original code does handle both fixed or variable length parameter codes.
Thanks for pointing that out to me.
ASKER
Follow Up:
Even though this clause ( WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%' ) does what I want it to, the performance of the SQL is now terrible.
I have gone back to splitting off the parameters into temp tables then reading that temp table in a IN statement in the WHERE statement.
h.category IN(SELECT categoryid
FROM #category
WHERE h.category = categoryid)
Much Much Much FASTER.
Even though this clause ( WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%' ) does what I want it to, the performance of the SQL is now terrible.
I have gone back to splitting off the parameters into temp tables then reading that temp table in a IN statement in the WHERE statement.
h.category IN(SELECT categoryid
FROM #category
WHERE h.category = categoryid)
Much Much Much FASTER.
I would recommend using a table-valued split function (there are many examples on the internet...pick one that matches your needs) and join against that return using LIKE.
SELECT ...
FROM myTable AS h
INNER JOIN dbo.Split(@pcategoryo, ',') AS category
ON h.category LIKE '%' + category.value + '%'