How to Insert Data Using Stored Procedure in SQL Server
How to insert rows with an INSERT query from a stored procedure with SQL Server? What option to display the number of rows inserted by the stored procedure? How to write a SQL Server procedure with an INSERT?
First, check beforehand that the example table does not exist, if it exists then we delete it with the DROP TABLE command. Then create the CLIENTS table which will be used to insert the lines.
|
1 2 3 4 5 6 7 8 9 10 |
IF exists( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'Clients') AND type in (N'U')) DROP TABLE Clients; CREATE TABLE Clients ( ID int IDENTITY(1,1), FirstName nvarchar(20), City nvarchar(20) ); |
How to write a SQL Server stored procedure with an INSERT command?
Here too, start by checking if the procedure already exists in the database and delete it if necessary. In a second step, create the stored procedure uspInsertClient to insert in the table of customers two fields passed in parameters, the name and the city.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
IF EXISTS( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'uspInsertClient') AND type in (N'P') ) DROP PROCEDURE uspInsertClient; GO CREATE PROCEDURE uspInsertClient @FirstName nvarchar(20), @City nvarchar(20) AS INSERT INTO Clients(FirstName, City) VALUES (@FirstName, @City); GO -- 3 Inserted clients SET NOCOUNT OFF; EXECUTE uspInsertClient @FirstName = 'Aathem', @City = 'LA'; EXECUTE uspInsertClient @FirstName = 'Sam', @City = 'WDC'; EXECUTE uspInsertClient @FirstName = 'ISAAC', @City = 'MC'; |
How not to display the number of rows affected by the INSERT query in the stored procedure?
The “SET NOCOUNT ON” command tells SQL Server not to return the number of affected rows, in our case inserted by the INSERT SQL statement. Thus, if the option is activated, ie “SET NOCOUNT ON”, the number of lines is not reassembled, as in the example below:
|
1 2 3 4 5 |
EXECUTE uspInsertClient @FirstName = 'Aathem', @City = 'LA'; EXECUTE uspInsertClient @FirstName = 'Sam', @City = 'WDC'; EXECUTE uspInsertClient @FirstName = 'ISAAC', @City = 'MC'; |
Finally check the result of the INSERT query with a SELECT command on the Clients table.
|
1 2 3 |
SELECT * FROM Clients; |
