Wildcard as parameter of stored procedure

sql stored-procedures interbase select-query
Andrew Vershinin picture Andrew Vershinin · Mar 2, 2014 · Viewed 9.1k times · Source

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?

Answer

user3358344 picture user3358344 · Mar 2, 2014

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More