Insert data from a SQL Server procedure

How to insert data from a SQL Server stored procedure?

Tutorial on how to insert data into a SQL Server table from inside a stored procedure. 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.

1. How to insert data from a SQL Server stored procedure ?

This is a 3 step tutorial to learn how to insert rows from a stored procedure with variables.

1.1 Create the sales table

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

1.2 Example of SQL Server stored procedure to 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.

2. 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.

Return values with SQL Server stored procedure