Declaring Datatypes for a Parameter in a Stored Procedure

Use the declare function to declare the datatype for a parameter in a stored procedure.

create procedure [owner.]procedure_name [;number] 
		[[(]@parameter_name datatype [= default] [output] 
			[,@parameter_name datatype [= default] 
				[output]]...[)]]
[with recompile] 
as SQL_statements

Example 1

For example:

create procedure auname_sp @auname varchar(40) 
as 
    select au_lname, title, au_ord 
    from authors, titles, titleauthor 
    where @auname = au_lname 
    and authors.au_id = titleauthor.au_id 
    and titles.title_id = titleauthor.title_id