Create Stored Procedure

Question

Hi,
If we create a stored procedure where and how is it saved and how is error handling done to the procedure. If any error occours where is it thrown. Help me.
Thanx,
Yamini.

Mahesh · Answer

If a stored procedure is created it is saved in the particular database in which u compile ie.
in sql server 2005/2000
if u r in pubs database(use pubs)
u create a sp like
create proc Test
@Out_Max int output
as
begin
select @Out_Max = max(id) from
/*Error Handling */
if(@@error<>0 OR @@rowcount=0)
begin
print 'error'
end
else
print 'success'
end
end
When u press F5 it compiles and a new sp by name Test is created in pubs database
to exec sp
declare @outmax int
exec Test @Out_Max = @outmax output
press F5
it shows the output
Here simple error handling is done using @@error and @@rowcount global variables
in sql server 2005 u can use try catch as in any modern language
Hope this helps u