1. Example. STORE PROCEDURE.sql

IF EXISTS(SELECT 1 FROM sys.procedures WHERE name = 'spCustomerIns')
    DROP PROC spCustomerIns
GO
CREATE PROC spCustomerIns(
    @pId        INT OUT,
    @pFirstName NVARCHAR(MAX),
    @pLastName  NVARCHAR(MAX),
    @pCity      NVARCHAR(MAX),
    @pCountry   NVARCHAR(MAX),
    @pPhone     NVARCHAR(MAX)
)
AS
    BEGIN
        SELECT @pId = ISNULL(MAX(cus.Id), 0) + 1  FROM Customer cus
 
        INSERT INTO Customer(Id, FirstName, LastName, City, Country, Phone)
            VALUES(@pId, @pFirstName, @pLastName, @pCity, @pCountry, @pPhone)
    END
GO

SELECT *
FROM Customer

/** Output **/
Id          FirstName                                LastName                                 City                                     Country                                  Phone
----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------------
...
90          Matti                                    Karttunen                                Helsinki                                 Finland                                  90-224 8858
91          Zbyszek                                  Piestrzeniewicz                          Warszawa                                 Poland                                   (26) 642-7012
93          Jean-Guy                                 Lauzon                                   Montréal                                 Canada                                   (514) 555-9022
94          Chantal                                  Goulet                                   Ste-Hyacinthe                            Canada                                   (514) 555-2955
 
SET IDENTITY_INSERT Customer ON
EXEC spCustomerIns 95, 'Pascual', 'DiBella Nava', 'Tampico', 'México', '212-98-43'
-- SET IDENTITY_INSERT Customer OFF
 
SELECT *
FROM Customer

/** Output **/
Id          FirstName                                LastName                                 City                                     Country                                  Phone
----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- --------------------
...
90          Matti                                    Karttunen                                Helsinki                                 Finland                                  90-224 8858
91          Zbyszek                                  Piestrzeniewicz                          Warszawa                                 Poland                                   (26) 642-7012
93          Jean-Guy                                 Lauzon                                   Montréal                                 Canada                                   (514) 555-9022
94          Chantal                                  Goulet                                   Ste-Hyacinthe                            Canada                                   (514) 555-2955
95          Pascual                                  DiBella Nava                             Tampico                                  México                                   212-98-43