Conditional statements allow branching within stored procedures in a fashion
similar to other languages, like Visual Basic. The example below returns the
matching invoice if a non-zero value is passed to it, otherwise it raises an error.
create procedure proc_rtv_invoice (@inv_id numeric(8,0) as
if @inv_id > 0
select inv_id, inv_date, sales_rep_id
from invoice
where inv_id = @inv_id
else
raiserror 99999 'Error: invalid invoice #'
return
go
Another example, which illustrates the begin and end constructs
create procedure proc_rtv_invoice (@inv_id numeric(8,0)) as
declare @date datetime
if @inv_id > 0
begin
select @date = getdate()
select inv_id, inv_date, sales_rep_id,@date
from invoice
where inv_id = @inv_id
end
else
raiserror 99999 'Error: invalid invoice #'
return
go
Note how the variable @date was declared, and given a value.
|
|
