Stored Procedure in SQL Server

A simple format for Stored Procedure :

CREATE PROCEDURE Procedure_Name
         @i_parameter1        DataType
        ,@i_parameter2        DataType
AS

BEGIN TRY
        'Actual Processing
END TRY

BEGIN CATCH
        'Error Handling
END CATCH

Where DataType could be int, varchar, datetime etc. You can do the error handling in the catch block. This feature is available in SQL 2005 onwards.

Also there is concept of OUT variable in SQL. Whatever value is stored in OUT variable that an be used even after the execution of the Stored Procedure. Just mention OUT after the name of the variable. For more information on OUT parameter visit http://msdn.microsoft.com/en-us/library/ms378108(v=sql.90).aspx

Hope this helps.
All the best.

Hi Stephen,
                here is simple syntax for stored procedure in sql server.

 use databasename
CREATE PROCEDURE Procedure_Name
 -- Add the parameters for the stored procedure here if you want
 @Param1 Datatype_For_Param1,
 @Param2 Datatype_For_Param2
AS
BEGIN
 
  
 SELECT * from TableName with(nolock)
END
GO


call it like..... exec Procedure_Name param1_value param2_value.


Thanx,
Pravin.

See here: http://www.c-sharpcorner.com/UploadFile/raj1979/SQLViewsStoredProcedures10032008152021PM/SQLViewsStoredProcedures.aspx