How to insert data from a SQL Server stored procedure?
Tutorial on how to insert data from a SQL Server stored procedure using T-SQL variables. And also how to show or hide the number of rows inserted by the procedure. This example of a T-SQL procedure inserts rows into a customer table. This is a 3 step tutorial to learn how to insert rows from a stored procedure with variables.
Table of Contents
Create the sales table to use in the procedure
First, check that the example table does not exist. If the table exists, then delete it with the DROP TABLE command. Then create the customers table to insert the rows of the example.
-- Test if the table CUSTOMERS already exists -- And delete it if necessary IF EXISTS( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[dbo].[CUSTOMERS]') AND type in (N'U') ) DROP TABLE [dbo].[CUSTOMERS] GO -- Create the CUSTOMERS table with the column NAME declared as UNIQUE -- The UNIQUE keyword defines the column with a unique value -- Inserting two customers with the same name is therefore impossible CREATE TABLE [dbo].[CUSTOMERS] ( [CLIENTID] int IDENTITY(1,1), [NAME] nvarchar(20) UNIQUE, [CITY] nvarchar(20) ) GO
Create the SQL Server stored procedure and insert data
First check if the stored procedure already exists in the database and delete it if necessary. Secondly, create the stored procedure uspInsertClient to insert two fields passed as parameters, the name and the city, into the customer table.
IF EXISTS( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'uspInsertCustomer') AND type in (N'P') ) DROP PROCEDURE uspInsertCustomer; GO CREATE PROCEDURE dbo.uspInsertCustomer @Name nvarchar(20), @City nvarchar(20) AS BEGIN INSERT INTO dbo.Customers (Name, City) VALUES (@Name, @City); END; GO
Run the procedure three times to insert new customers into the table.
-- Deactivate the option to hide the number of insertions SET NOCOUNT OFF; -- Insert three new customers EXECUTE dbo.uspInsertCustomer @Name = 'MAMMADOU', @City = 'Toulouse'; EXECUTE dbo.uspInsertCustomer @Name = 'KARIM', @City = 'Nantes'; EXECUTE dbo.uspInsertCustomer @Name = 'ISAAC', @City = 'Versailles';
This example of an insert query in the SQL Server stored procedure is used to insert rows into a table. The code checks for the existence of the procedure beforehand to avoid errors on creation. The code makes it easy to call the created procedure with the parameters. And thus to standardise the insertion of data in this table.
Option to hide the number of rows inserted
When you insert data from a SQL Server procedure, by default, the system displays the number of rows inserted. The SET NOCOUNT ON command tells SQL Server not to display the number of rows affected. In our case, inserted by the SQL INSERT query. Thus, if the option is enabled, the number of rows is not shown, as in the example below. This display option allows you to avoid polluting files or log tables during the execution of complex stored procedures.
-- Deactivate the option to hide the number of insertions SET NOCOUNT ON; -- Insert three clients with the option enabled EXECUTE dbo.uspInsertCustomer @Name = 'ISACH', @City = 'Lille'; EXECUTE dbo.uspInsertCustomer @Name = 'AHMED', @City = 'Bordeaux'; EXECUTE dbo.uspInsertCustomer @Name = 'YANIS', @City = 'Marseille';
Finally, check the result of the query and read the data with a SELECT query on the customer table. Note that the three clients are present twice each in the table. This corresponds to the six executions of the SQL command. That is, three times with the row count display option on and three times with the display option off.
SELECT * FROM dbo.Customers;
This T-SQL development tutorial explains how to insert data from a SQL Server stored procedure. Finally, here is a tutorial on how to create a SQL stored procedure that returns values with the OUTPUT option.
Conclusion on data insertion using SQL Server stored procedures
This T-SQL tutorial explains how to create a sales table and a SQL Server stored procedure, and ultimately insert data using variables. The tutorial also teaches how to control the display of the number of rows inserted by using the SET NOCOUNT command. By following these steps, it is possible to effectively standardize data insertion and manage the display of row counts for more complex stored procedures.