Suppose, I have a stored procedure, that accepts one parameter - id, and returns some data corresponding to this id. What if I want to retrieve all such data, like using wildcard in SELECT query? How to do this?
Asked
Active
Viewed 1.2k times
1 Answers
6
You can add a trailing '%' to your query. Assume that @param is the parameter to your stored procedure:
declare @param2 varchar(100)
set @param2 = @param + '%'
select * from table where column like @param2
That will return a wildcard search beginning with the value in @param. For partial match use '%' + @param + '%'
[Edit]
Based on the below clarification in the comments:
if @id != '*'
begin
select * from table where column = @id
end
else
begin
select * from table
end
user3358344
- 193
- 1
- 9
-
Your answer is about text parameter only. What if passed parameter is integer (for example, a primary key)? – Andrew Vershinin Mar 02 '14 at 12:07
-
That was what I thought you meant :) Do you mean if the the search parameter is 1, you want to return all primary keys including 1, 11, 112, and so on? – user3358344 Mar 02 '14 at 12:19
-
I mean, if I want row with id = 1, I pass 1 as the parameter, 2 -> 2 as the parameter, but what if I want all existing rows? – Andrew Vershinin Mar 02 '14 at 12:27
-
Thanks. I did this thing by if-else statement, but thought if there is more elegant way to do that. – Andrew Vershinin Mar 02 '14 at 12:36