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