How to Create Stored Procedure in SQL

In this article I am going to explain about how to create stored procedure.
  • 2726

Introduction

CREATE PROC statement is used to create a stored procedure. During creating a stored procedure we need to remember that stored procedure names can't be same as the name of any other object of database.

Here I am going to explain some basic terms of stored procedure

  • CREATE PROC Statement is used to create a stored procedure.
  • CREATE PROC  Statement must be first statement in batch.
  • We can create temporary stored procedure in SQL server. In temporary stored procedure we use # sign as prefix with name in local stored procedure and ## sign as prefix with name of global stored procedure.
  • You can pass one or more parameter in stored procedure from calling program of stored procedure.
  • WITH RECOMPILE Statement restrict the stored procedure to be recompiled every time when it is used in application. It reduces system performance so avoid it.
  • WITH ENCRYPTION Statement in Stored prevents users to view the code of stored procedure.

A Statement that create vendors table

Clipboard08.jpg

Statements that insert data in vendors table

Clipboard10.jpg

A Statement that is used to fetch data from vendors table

Clipboard14.jpg

A Statement that creates a Stored Procedure

Clipboard20.jpg

A Statement that run the Stored Procedure

Clipboard03.jpg

A Statement that fetch data from vendorscopy table created by stored procedure

Clipboard22.jpg


© 2020 DotNetHeaven. All rights reserved.