{"id":8804,"date":"2023-11-17T06:36:00","date_gmt":"2023-11-17T05:36:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=8804"},"modified":"2023-11-22T18:25:48","modified_gmt":"2023-11-22T17:25:48","slug":"sql-server-stored-procedure","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/sql-server-stored-procedure\/","title":{"rendered":"How to create a SQL Server stored procedure ?"},"content":{"rendered":"\n

T-SQL tutorial on how to create and run basic SQL Server stored procedures.<\/em><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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).<\/p>\n\n\n\n

1. Select data using a SQL stored procedure<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

SELECT *\nFROM   dbo.CLIENTS;\n<\/pre>\n\n\n\n
The code to create the clients table is available in this post about how to create and manage SQL Server tables<\/a><\/strong>.<\/p>\n\n\n\n
-- Create the Clients table\nCREATE TABLE [dbo].[CLIENTS] (\n   [CLIENTID] int IDENTITY(1,1),\n   [NAME] nvarchar(20) UNIQUE,\n   [CITY] nvarchar(20)\n)\nGO\n\n-- Insert data for the example\nINSERT INTO dbo.CLIENTS (NAME, CITY) VALUES ('MAMMADOU', 'Lyon');\nINSERT INTO dbo.CLIENTS (NAME, CITY) VALUES ('SERGEI', 'Lyon');\nINSERT INTO dbo.CLIENTS (NAME, CITY) VALUES ('CHRISTOPHE', 'Paris');\n\n-- Check inserted rows\nSELECT *\nFROM   dbo.CLIENTS;\n<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
CREATE PROCEDURE uspClientsList\nAS\n   SELECT *\n   FROM dbo.CLIENTS\nGO\n<\/pre>\n\n\n\n
2. Example script to execute a T-SQL procedure<\/h2>\n\n\n\n
To execute the stored procedure and display the contents of the Customers table<\/a> specified in the query, simply execute the following code:<\/p>\n\n\n\n
EXECUTE dbo.uspListeClients;\nGO\n\n-- Or\nEXEC dbo.uspClientsList;\nGO\n\n-- Or simply\nuspClientsList;\nGO\n<\/pre>\n\n\n
\n
\"Create
Create a SQL Server stored procedure to select data from a table<\/em><\/figcaption><\/figure><\/div>\n\n\n
3. Two things to know about T-SQL stored procedures<\/h2>\n\n\n\n