Create Stored procedure to insert record into SQL Server database

Updated on August 30, 2015     Kisan Patel

This tutorial will show you how to create a stored procedure to insert record into SQL Server database?

Create a stored procedure and update the default template code with below−

CREATE PROCEDURE InsertRegionDetails
-- Add the parameters for the stored procedure here
@RegionDesription  varchar(50),
@Age smallint
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  -- Insert statements for procedure here
  INSERT INTO Region
    (RegionDescription,  AGE)
  VALUES
    (@RegionDesription, @Age)
END

Here, we have 2 parameters into the stored procedure that is separated by comma. Notice the data type and size of the parameters. It must match with the field type specified in the database table column.

Notice the INSERT into statement. After writing the table name, the column names should be written under bracket and separated by comma and then VALUES and again the input parameters coming in to this stored procedure for respective column names of the table.

Now executing the above SQL statements will create a new stored procedure in the database.

To execute this stored procedure, we can either use EXEC statement as explained above or right click the stored procedure and choose Execute Stored Procedure… option.

This will bring Execute Procedure dialog box with equal number of rows and value textbox as the stored procedure parameters. We need to write necessary data for the respective parameter value box and click OK button.

Clicking OK button opens up a new query window with same EXEC statement that we have used in previous topics. Notice the EXEC statement and parameters value specified. This dialog box does the same thing that we had done manually by writing EXEC statement.
The Return Value is 0 as this stored procedure is not returning any value.