How to insert data using a SQL Server stored procedure? T-SQL

How to use T-SQL variables to insert data from using 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

1. Create the sales table to use in the procedure

First, check that the example customers 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

2. Create the 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';

*Insert data from a SQL Server stored procedure with SSMS*

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.

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