SQL Server > Stored Procedure

Insert record stored procedure in SQL Server

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 InsertPersonalDetails
       -- Add the parameters for the stored procedure here
       @FirstName varchar(50),
       @LastName varchar(50),
       @Age smallint,
       @Active bit
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.

    -- Insert statements for procedure here
       INSERT INTO PersonalDetails
              (FirstName, LastName, Age, Active)
              (@FirstName, @LastName, @Age, @Active)

Here, we have four 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.

 Views: 1598 | Post Order: 89

Write for us