March 12, 2012 at 9:25 am
I have a stored procedure with a SQL statement like this
@FirstName as varchar(10)
AS
SELECT * from dbo.mytable WHERE FIrstName LIKE ‘%’ + @FirstName + ‘%’
This works great, but if someone puts in a “%” in the TextBox it brings down the whole database. Is there a way to write the SQL a little bit better?
March 12, 2012 at 9:30 am
Remove all occurrences of % from the input string using
REPLACE(@FirstName , '%','') and then pass it on to query!
March 12, 2012 at 9:30 am
you could wrap the variable in a replace function before passing it into the like clause
March 12, 2012 at 9:45 am
I changed it to
SELECT * from dbo.mytable WHERE FirstName LIKE ‘%’ + REPLACE(@FirstName , ‘%’,’’) + ‘%’ but it still seems to put down the whole database if I do a %%
March 12, 2012 at 9:47 am
you might want to take a look at full text indexing and the contains function then, otherwise you will be wrapping the variable in a multiple of replace statements for 2%'s 3%'s 4%'s etc etc it will just become hard to read properly.
March 12, 2012 at 9:50 am
marly (3/12/2012)
I changed it to
SELECT * from dbo.mytable WHERE FirstName LIKE ‘%’ + REPLACE(@FirstName , ‘%’,’’) + ‘%’but it still seems to put down the whole database if I do a %%
What do you mean 'put down the whole database'? Does it hang, or does it err?
Do you get the same results if the text box is empty?
_________________________________
seth delconte
http://sqlkeys.com
March 12, 2012 at 9:53 am
seth delconte (3/12/2012)
marly (3/12/2012)
I changed it to
SELECT * from dbo.mytable WHERE FirstName LIKE ‘%’ + REPLACE(@FirstName , ‘%’,’’) + ‘%’but it still seems to put down the whole database if I do a %%What do you mean 'put down the whole database'? Does it hang, or does it err?
Do you get the same results if the text box is empty?
Sorry it's a typo. I mean, it will "pull down the whole database", as in it pulls all records from the DB.
I was hoping there would be an easy fix in the stored procedure, then I only need to change the stored procedure and not the application.
March 12, 2012 at 9:57 am
Bro, when u see a empty string in the Frist Name text box (or wherever the value for @FirstName comes from) , handle it. Tell the user that it is ging to pull every other row ifrom the database and only upon the user's confirmation, proceed. Replace did not induce this, the way yu have ur query does!
March 12, 2012 at 10:00 am
ColdCoffee (3/12/2012)
Bro, when u see a empty string in the Frist Name text box (or wherever the value for @FirstName comes from) , handle it. Tell the user that it is ging to pull every other row ifrom the database and only upon the user's confirmation, proceed. Replace did not induce this, the way yu have ur query does!
I know, that's why I posted the question in the first place and said, "Is there a way to write the SQL a little bit better?"
So again, is there a better way to handle this?
March 12, 2012 at 10:05 am
marly (3/12/2012)
ColdCoffee (3/12/2012)
Bro, when u see a empty string in the Frist Name text box (or wherever the value for @FirstName comes from) , handle it. Tell the user that it is ging to pull every other row ifrom the database and only upon the user's confirmation, proceed. Replace did not induce this, the way yu have ur query does!I know, that's why I posted the question in the first place and said, "Is there a way to write the SQL a little bit better?"
So again, is there a better way to handle this?
Yes.
1- Do not allow to enter and empty search string - send a message back to the user.
2- Do not allow to enter a '%' in search string - send a message back to the user.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 12, 2012 at 10:11 am
I think that you may be looking to include the % as a search term as in the demo below. If this is the case use the replace technique as shown before but add square brackets around the %.
create table dbo.MyTable(
MyValue varchar(30)
)
insert into dbo.MyTable values
('Fred Jones'),
('KPI Value %'),
('%Increase'),
('As % of total')
go
create proc dbo.Find @passedvalue varchar(30)
as
select * from dbo.MyTable where MyValue LIKE '%' + replace(@passedvalue,'%','[%]')+'%'
go
exec dbo.Find 'F' -- find all rows where value includes F
exec dbo.Find '%' -- find all rows where the character % exists
March 12, 2012 at 10:53 am
Mark Fitzgerald-331224 (3/12/2012)
I think that you may be looking to include the % as a search term as in the demo below. If this is the case use the replace technique as shown before but add square brackets around the %.
This seems to have done the trick, thanks!
March 12, 2012 at 12:57 pm
In addiction to input validation (maybe) you can put a TOP clause to prevent too many records to be fetched.
If its a web or web like app you can take advantage of server-side paging to prevent waste of bandwith with data the user ill current no see.
March 12, 2012 at 1:16 pm
jcb (3/12/2012)
In addiction to input validation (maybe) you can put a TOP clause to prevent too many records to be fetched.If its a web or web like app you can take advantage of server-side paging to prevent waste of bandwith with data the user ill current no see.
It was not an issue of input validation or amount of records. It was simply that the query was not defined properly and did not return the desired results. I think now that the OP knows that % must be in brackets when being used as a literal, the issue is fixed.
Jared
CE - Microsoft
March 12, 2012 at 1:22 pm
Jared,
I just point that out because a user can input "",".","mary" or anything can fetch too many records depending on OP data.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply