How to write- create Stored Procedure in SQL Server

19 Flares Filament.io 19 Flares ×

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.(Source: MSDN).To create stored procedure in SQL Server,Go to Database ->Programmability->Stored Procedures.Click right click and click on New Stored Procedure.It will create a new Query Window for you.

create-stored-procedure-in-SQL-Server

You will get above image script in your SQL Server.In this script,you have to modify three things.

  • Stored Procedure Name
  • Input Parameters (Optional)
  • Logical statements of stored procedure

Stored Procedure Name

  • To write stored procedure name,Replace your stored procedure Name with “<Procedure_Name, sysname, ProcedureName>” .
  • Write your stored procedure’s name logical,so anyone can understand easily.e.g. If you want to create stored procedure for Inserting data name it Insert_Product.So in general Operation_TableName

Input Parameters

  • Add them below “Add the parameters for the stored procedure here” statement.
  • Input parameters are those parameters which returns results dynamically.e.g. for some particular ProductId you want to retrieve Product’s Name.
  • Again,Name your Input parameters wisely like Id or ProductId not like x,y.!
  • Give appropriate data-type like INT,Varchar(50) etc. according to your Query’s condition.
  • If you have want some table’s data directly,then no need to add any input parameter.

Logical statements of stored procedure

  •  You can write your required statements after “SET NOCOUNT ON;”.
  • You can also declare required variables as you want.e.g. DECLARE @Name Varchar(50).
  • You can write your queries as you want.I have write below stored procedure to retrieve Product’s Name & Price from input’s ProductId’s value.!
  • After writing your stored procedure,click on Parse(√),It notifies you about errors of your stored procedure.
  • If everything is fine,Click Execute button or Press F5 to execute script which will crete stored procedure for you.
  • If you want to make any changes to stored procedure,Just right click on stores procedure 7 click Modify or write ALTER instead of CREATE.

Now pass any ProductId from your business Logic & it will return Product’s Name & Price.Hope you will be able to create stored procedure after this SQL tutorial.

Related Reading:

One thought on “How to write- create Stored Procedure in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">