Create Simple Stored Procedure-SQL Server

Title

Create Simple Stored Procedure-SQL Server

Introduction

Level: Beginners

In this post we’ll see how to create a Stored Procedure in SQL Server.

What is Stored Procedure?

A Stored Procedure is a set of SQL statements with an assigned name that is stored in database in a compiled form so that it can be shared with number of programs.

Advantages of Stored Procedure

• Controlling access to data
• Increases Productivity
• Preserving Data Integrity

Let’s see how we can create a simple Stored Procedure, see below code.

CREATE PROCEDURE WP_Test
AS
 BEGIN

 DECLARE @TestTable TABLE
 (
 [Post_ID] INT NOT NULL,
 [Author] VARCHAR(MAX) NULL,
 [Title] VARCHAR(MAX) NULL
 )

 INSERT INTO @TestTable
 SELECT wpp.[ID] AS [Post_ID],
 wpu.[user_login] AS [Author],
 wpp.[post_title] AS [Title]
 FROM dbo.wp_posts wpp, dbo.wp_users wpu
 WHERE wpu.id=wpp.post_author
 
 --Final Result
 SELECT [Post_ID], [Author], [Title]
 FROM @TestTable
 END
GO

Explanation

CREATE PROCEDURE  procedure_name

AS

        //Set of SQL Query

GO

This command helps in creating stored procedure, and set of SQL Query is written in AS GO block.

Query structure starts with the BEGIN and ends with END statements.

DECLARE @TestTable TABLE
(
[Post_ID] INT NOT NULL,
[Author] VARCHAR(MAX) NULL,
[Title] VARCHAR(MAX) NULL
)

INSERT INTO @TestTable
SELECT wpp.[ID] AS [Post_ID],
wpu.[user_login] AS [Author],
wpp.[post_title] AS [Title]
FROM dbo.wp_posts wpp, dbo.wp_users wpu
WHERE wpu.id=wpp.post_author

--Final Result
SELECT [Post_ID], [Author], [Title]
FROM @TestTable

Stored Procedure(SP) are normally used where we need to fetch data from many different tables on various conditions, which is hassle to write again and again. So we create a stored procedure in which we mention all our logic and compiled it and store it in database, this saves our time of writing query again and again.




So in above code, we have created a temporary table in SP as TestTable with columns as Post_ID, Author, and Title, and these results are fetched from two different tables i.e. dbo.wp_posts (which contains posts acheter viagra data) and dbo.wp_users (which store user information).

Now we will Insert data into our TestTable which is fetched from both the tables, and finally SELECT data from the TestTable and it will be displayed when SP is executed.

To execute SP,

exec WP_Test

Output