January 21, 2014 at 2:12 pm
I am trying to pass wildcard parameter through the Query instead of filters.
Select FName,Lname from Customers
where ID = @ID
The above works fine. Now when I try with wildcards I get blank row..
I tried
Select FName,Lname from Customers
where ID = '%' + @ID+ '%'
Any ideas?
January 21, 2014 at 2:32 pm
For your wildcard search portion, you need to use LIKE instead of the = operator; so:
Select FName,Lname from Customers
where ID LIKE '%' + @ID+ '%'
By using = instead of LIKE, you're looking specifically for %@ID% (replacing the value of @ID); using LIKE causes the wildcards to be interpreted correctly.
I'd be careful of using this sort of query, though; without the proper proofing, it's open to SQL injection, and all of its assorted security risks. EDIT: Or not at all. I should probably use a little more comprehension!
-
January 21, 2014 at 3:00 pm
Yes, I see that. But how can I also see all the values through wildcard.
I mean I should be able to pass "*" and see all aswell
January 21, 2014 at 3:01 pm
hisakimatama (1/21/2014)
I'd be careful of using this sort of query, though; without the proper proofing, it's open to SQL injection, and all of its assorted security risks.
Can you explain how can this be used for SQL Injection? Unless the query becomes dynamic, concatenating values to a variable used in a like comparison won't generate SQL Injection.
DECLARE @ID varchar(8000) = '1; SELECT * FROM sys.databases;'
Select *
from SomeTable
where table_id LIKE '%' + @ID+ '%'
January 21, 2014 at 3:09 pm
It will be passed through a dataset so the values will be entered at runtime.
So, I wont declare in the dataset...
January 21, 2014 at 3:13 pm
sharonsql2013 (1/21/2014)
Yes, I see that. But how can I also see all the values through wildcard.I mean I should be able to pass "*" and see all aswell
Hm. If you specifically want to pass an asterisk, it'll require a check to see if an asterisk was entered, and then blanking out @ID; this will make you do a search of LIKE '%%', which will wildcard-return all values. That would be the simplest way, if a bit shaky.
Luis, after a bit of tinkering in SSRS myself, I've clearly misspoken! I suppose I've got another complaint to lodge with our vendor, regrettably. Threw together a basic report and tried to injection-drop a dummy table, and nothing happened. Tried to injection-drop via a vendor report... Success! Not a good thing! I'll retract that earlier point.
-
January 21, 2014 at 3:22 pm
hisakimatama (1/21/2014)
Luis, after a bit of tinkering in SSRS myself, I've clearly misspoken! I suppose I've got another complaint to lodge with our vendor, regrettably. Threw together a basic report and tried to injection-drop a dummy table, and nothing happened. Tried to injection-drop via a vendor report... Success! Not a good thing! I'll retract that earlier point.
Kill the vendor!... Ok, a complaint is fine for now.
Good luck!
January 21, 2014 at 3:37 pm
Select *
from SomeTable
where table_id LIKE '%' + @ID+ '%' OR table_id LIKE '%'
Well, it always give all values irrespective of parameter.
January 21, 2014 at 3:43 pm
Here is what I was looking for
Select *
from SomeTable
where (table_id LIKE '%' + @ID+ '%' OR @ID = '*')
January 21, 2014 at 3:44 pm
sharonsql2013 (1/21/2014)
Select *from SomeTable
where table_id LIKE '%' + @ID+ '%' OR table_id LIKE '%'
Well, it always give all values irrespective of parameter.
That's because "OR table_id LIKE '%'" makes your WHERE clause to evaluate as true for any rows.
January 22, 2014 at 6:01 am
sharonsql2013 (1/21/2014)
Here is what I was looking forSelect *
from SomeTable
where (table_id LIKE '%' + @ID+ '%' OR @ID = '*')
Terribly sorry for the late reply; I left work shortly after my last one, and got a bit tangled up afterwards.
In any case, what you're looking for is to give your users the ability to enter an ID to pull that specific ID, and an asterisk to pull everything, correct? If so, it could be accomplished like so:
IF @ID = '*'
SELECT * FROM SomeTable
ELSE
SELECT * FROM SomeTable WHERE table_id LIKE '%'+@ID+'%'
Alternatively, you could replace the IF @ID check's asterisk with anything; I usually use "All Items" or something similar in my reports, so it's more intuitive for the users.
-
January 27, 2014 at 7:32 pm
Thank you.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy