August 5, 2013 at 7:38 am
Hello All,
I'm using the procedure to return results to an Access 2010 .adp:
I would like to return a result if the user enters the following:
The search criteria SubjectNumber = 'UK01' would return all SubjectNumbers starting with 'UK001...'. and so on. However, no matter how I write the SQL, it will only return a result for a complete SubjectNumber ('UK010001').
I have tried the following in an SQL Server query, which returns the result I expect. So what's wrong with the SQL in my stored procedure?
Thanks for any help.
select *
from tblPerson
where SubjectNumber like '%'+ 'UK' +'%'
Stored Procedure:
@SubjectNumber char(9)
AS
SELECT PersonID, SubjectNumber, NHSNo, Postcode
FROM tblPerson
WHERE (@SubjectNumber IS NULL OR SubjectNumber LIKE '%' + @SubjectNumber + '%')
August 5, 2013 at 7:50 am
The way you've written it, it will return everything that has 'UK' in. If you lose the first '%' then you'll get everything that starts with 'UK'. Some sample data and expected results would make it a lot clearer what you're trying to achieve.
John
August 5, 2013 at 7:53 am
M Joomun (8/5/2013)
Hello All,I'm using the procedure to return results to an Access 2010 .adp:
I would like to return a result if the user enters the following:
The search criteria SubjectNumber = 'UK01' would return all SubjectNumbers starting with 'UK001...'. and so on. However, no matter how I write the SQL, it will only return a result for a complete SubjectNumber ('UK010001').
I have tried the following in an SQL Server query, which returns the result I expect. So what's wrong with the SQL in my stored procedure?
Thanks for any help.
select *
from tblPerson
where SubjectNumber like '%'+ 'UK' +'%'
Stored Procedure:
@SubjectNumber char(9)
AS
SELECT PersonID, SubjectNumber, NHSNo, Postcode
FROM tblPerson
WHERE (@SubjectNumber IS NULL OR SubjectNumber LIKE '%' + @SubjectNumber + '%')
Hard to know for sure but I suspect the problem is that you have a fixed length datatype char(9). That means your query is very likely to not find any results.
The following code is very similar to your search but uses sys.objects so I know it is something you have.
declare @SearchVal char(50) = 'sysrowsets'
select *
from msdb.sys.objects
where name like @SearchVal + '%'
This won't return anything because there are no rows in that table with a value of "sysrowsets ".
Change the variable to varchar and voila!
declare @SearchVal varchar(50) = 'sysrowsets'
select *
from msdb.sys.objects
where name like @SearchVal + '%'
I would also warn you that putting the wildcard at the front of your search renders your predicate nonSARGable. Also, since this is a search and is the begging of a catch-all query you should read this article that explains this type of query.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 8:00 am
That's just the problem. The stored procedure only returns a result if I enter a complete SubjectNumber, i.e. 'UK010001'. If I enter 'UK01', it should return all records that start with 'UK01...' but returns nothing.
August 5, 2013 at 8:03 am
Sean Lange (8/5/2013)
Hard to know for sure but I suspect the problem is that you have a fixed length datatype char(9). That means your query is very likely to not find any results.
The following code is very similar to your search but uses sys.objects so I know it is something you have.
declare @SearchVal char(50) = 'sysrowsets'
select *
from msdb.sys.objects
where name like @SearchVal + '%'
This won't return anything because there are no rows in that table with a value of "sysrowsets ".
Change the variable to varchar and voila!
declare @SearchVal varchar(50) = 'sysrowsets'
select *
from msdb.sys.objects
where name like @SearchVal + '%'
I would also warn you that putting the wildcard at the front of your search renders your predicate nonSARGable. Also, since this is a search and is the begging of a catch-all query you should read this article that explains this type of query.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Thanks for the reply.
Are you saying that I should change the data type to varchar instead of char?
August 5, 2013 at 8:06 am
Yes, thanks Sean. Changing the dataype has worked.
August 5, 2013 at 8:12 am
M Joomun (8/5/2013)
Yes, thanks Sean. Changing the dataype has worked.
Glad that worked for you. More importantly, do you understand why that worked?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 8:16 am
Sean Lange (8/5/2013)
M Joomun (8/5/2013)
Yes, thanks Sean. Changing the dataype has worked.Glad that worked for you. More importantly, do you understand why that worked?
Because it's looking for a fixed length value of 9 characters and I sent it a value with (for example) only 4 characters?
August 5, 2013 at 8:26 am
M Joomun (8/5/2013)
Sean Lange (8/5/2013)
M Joomun (8/5/2013)
Yes, thanks Sean. Changing the dataype has worked.Glad that worked for you. More importantly, do you understand why that worked?
Because it's looking for a fixed length value of 9 characters and I sent it a value with (for example) only 4 characters?
Not exactly. What is happening is that it will append spaces to the end of your searchval so that the total length is 9 characters. Then it returns nothing because it doesn't find a row with UK01 + 5 spaces. You could also wrap your parameter with an rtrim to accomplish the same results (although I would recommend my first solution).
LIKE '%' + RTRIM(@SubjectNumber) + '%')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 8:49 am
Sean Lange (8/5/2013)
M Joomun (8/5/2013)
Sean Lange (8/5/2013)
M Joomun (8/5/2013)
Yes, thanks Sean. Changing the dataype has worked.Glad that worked for you. More importantly, do you understand why that worked?
Because it's looking for a fixed length value of 9 characters and I sent it a value with (for example) only 4 characters?
Not exactly. What is happening is that it will append spaces to the end of your searchval so that the total length is 9 characters. Then it returns nothing because it doesn't find a row with UK01 + 5 spaces. You could also wrap your parameter with an rtrim to accomplish the same results (although I would recommend my first solution).
LIKE '%' + RTRIM(@SubjectNumber) + '%')
Thanks for that Sean.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply