How to create and run a basic SQL Server stored procedure ?
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.
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 to update existing data? 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.
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). 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 sales table CREATE TABLE [dbo].[SALES] ( [Year] INT, [MonthName] NVARCHAR(50), -- alphanumeric [MonthCurrent] BIT, -- Boolean, 0 or 1 , false / true [NumberMonth] TINYINT, -- very small integer, from 0 to 255 [EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15 [NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31 [NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63 [Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma [Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma );
To create a simple stored procedure, use this code for example.
CREATE PROCEDURE uspClientsList AS SELECT * FROM dbo.CUSTOMERS GO
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
To create the stored procedure, 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.
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.
Conclusion on T-SQL stored procedures
In this tutorial, we learned how to create a simple 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.
A SQL Server stored procedure is a script written in Transact-SQL (T-SQL) and executed in a database. A procedure groups one or more SQL statements. It can handle input parameters and output parameters. The steps are repeated each time a stored procedure is called.