Toad World® Forums

Simple Queries with Variables


#1

This may be just a general ORacle Question or maybe not. I cannot seem to find any answers anywhere.

I want a simple query “select *” with a where clause that has a variable or Parameter. So when I execute the code I can plug in a value and all statements with that variable/parameter return the results. Like the example below.

Decalare &somevaraible := ‘somevalue’;
Select * from table1 where keyvalue = &somevariable;
Select * from table2 where keyvalue = &somevariable;
Select * from table3 where keyvalue = &somevariable;
end;


#2

You can write your query as a stored procedure and pass the parameter you want when you call the procedure.

When you write the procedure, make it so it accepts a parameter; then use that parameter in the query that follows.

create procedure [owner].[sp_name]
(@somevariable varchar(100))
as

Select * from table1 where keyvalue = @somevariable ;
Select * from table2 where keyvalue = @somevariable ;
Select * from table3 where keyvalue = @somevariable ;
end;


To call it, do exec [owner].[sp_name] ‘someinput’