Stored Procedure in SQL Server

Question

How can I make a Stored Procedure in SQL Server

Pravin Patil · Answer

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 H ope this helps. All the best.

Prabhu Raja · Answer

Hi, I had given a sample format of stored procedures, create procedure procedurename @userid int, -- Parameters to procedure @username varchar(50) As begin //Your Queries Here End Execute As, EXEC Procedurename parameter1,parameter2,... And also Read this Documentations How to: Create a Stored Procedure (SQL Server Management Studio) SQL Stored Procedures

Pravin More · Answer

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.

Mamta M · Answer

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

Stored Procedure in SQL Server

Answers (4)