How to create a SQL Server stored procedure ?

T-SQL tutorial on how to create and run basic SQL Server stored procedures.

To create a simple SQL Server stored procedure, use the CREATE statement. A SQL Procedure is nothing more than T-SQL code stored in a way that can be easily reused. In this short SQL Server tutorial here is how to write and use a simple stored procedure in Transact-SQL to select data from a table.

Before creating a stored procedure, you need to know what the end result will be. In other words, is the purpose to display data, insert data for example. Or it can be to update existing data, but it is also possible to delete data. That is to say, all Data Manipulation Language (DML) commands. And of course to combine several operations. Like for example calling one or more other procedures. This is called nested procedures.

On the other hand, all SQL object manipulation commands are also possible. That is to say all the commands known as Data Definition Language (DDL).

1. Select data using a SQL stored procedure

The purpose of this example is to select all the data of the customer table stored in the database. The table is in the default schema dbo, for Database Owner. Thus, the following T-SQL code returns all rows of this table.

SELECT *
FROM   dbo.CLIENTS;

The code to create the clients table is available in this post about how to create and manage SQL Server tables.

-- Create the Clients table
CREATE TABLE [dbo].[CLIENTS] (
   [CLIENTID] int IDENTITY(1,1),
   [NAME] nvarchar(20) UNIQUE,
   [CITY] nvarchar(20)
)
GO

-- Insert data for the example
INSERT INTO dbo.CLIENTS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon');
INSERT INTO dbo.CLIENTS (NAME, CITY) VALUES ('SERGEI', 'Lyon');
INSERT INTO dbo.CLIENTS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris');

-- Check inserted rows
SELECT *
FROM   dbo.CLIENTS;

To create a simple stored procedure, use this code for example. Simply use the CREATE PROCEDURE or CREATE PROC command. After the name of the stored procedure, use the keyword “AS”. The rest is just standard T-SQL code to execute.

CREATE PROCEDURE uspClientsList
AS
   SELECT *
   FROM dbo.CLIENTS
GO

2. Example script to execute a T-SQL procedure

To execute the stored procedure and display the contents of the Customers table specified in the query, simply execute the following code:

EXECUTE dbo.uspListeClients;
GO

-- Or
EXEC dbo.uspClientsList;
GO

-- Or simply
uspClientsList;
GO

3. Two things to know about T-SQL stored procedures

• First, do not use the “GO” keyword inside the stored procedure. This is because once the SQL Server compiler encounters “GO” it assumes that this is the end of the stored procedure.
• Finally, do not change the database inside the stored procedure. Either using the “USE AnotherBase” command for example does not work.

T-SQL stored procedures are widely used

In this tutorial, we learned how to create a basic SQL Server stored procedure using the CREATE statement, which helps store T-SQL code for easy reuse. We covered the process of defining the end result, incorporating Data Manipulation Language (DML) commands, and executing the stored procedure with the example code.

We also highlighted a few essential aspects to consider, such as not using the GO keyword inside the stored procedure and avoiding database changes inside the procedure code.

https://expert-only.com/en/t-sql/sql-server-pivot-query-example/