Stored Procedures: Parameters, Inserts and Updates

By Bill Graziano on 26 February 2001 | Tags: Stored Procedures

Darrell writes "I need to figure out how to run a query and get a recordset row. With the data from that recordset I need to create create a new row changing the primary key. The old row then needs to have status fields updated. I hope that is clear." I'll discuss passing parameters to stored procedures and using those parameters in SQL statements.

Earlier I wrote an overview article on stored procedures. This is kind of a part 2 to that article. I'll cover how to pass a parameter into a stored procedure and doing some simple inserts and updates. Let's assume your table looks like this:

MyTable
=============
PKValue int identity(1,1)
Field1 char(10)
Field2 char(10)
Status char(8)

I'll need to pass two parameters to our stored procedure. The first is the old primary key value and the second is the new status. I'm assuming that the PKValue field is an identity column so I don't need to supply it a new value.

Our create prodedure statement will look something like this:

CREATE PROCEDURE spNewValue  @pPKValue int, @pStatus char(8) AS

Notice that each parameter's data type is defined along with the parameters. Now we have the primary key of the record we wish to update and the new status value. Our first step is to create the new record. We'll use an INSERT/SELECT statement that looks like this::

INSERT MyTable
SELECT Field1,
  Field2,
  'Active' as Status
From MyTable
Where PKValue = @pPKValue

Notice that we didn't need to insert a value for PKValue since it's an identity column. I used my parameter in the WHERE clause to indicate which record I wanted. You can generally use a variable either side of the equal sign in a WHERE clause. You are comparing a column to a constant but the constant is in a variable. If you want table names or column names or an entire WHERE clause in your variable it gets more complicated. You can see the FAQ for more information.

The second step is to update the existing record with the new status value. That statement looks like this:

UPDATE MyTable
SET Status = @pStatus
WHERE PKValue = @pPKValue

Notice that I can also use a variable as the value for the SET clause of my UPDATE statement. And finally, the complete stored procedure:

CREATE PROCEDURE spNewValue  @pPKValue int, @pStatus char(8) AS 

SET NOCOUNT ON

INSERT MyTable
SELECT Field1,
  Field2,
  'Active' as Status
From MyTable
Where PKValue = @pPKValue

UPDATE MyTable
SET Status = @pStatus
WHERE PKValue = @pPKValue

go

I include a SET NOCOUNT ON so that now extra rows are returned to my client application. You can use the EXEC statement to call a stored procedure. My EXEC statement for this procedure looks like this:

exec spNewValue @pPKValue = 1, @pStatus = 'Inactive'

You don't have to specify the parameter names when you call the procedure if you keep your parameters in the same order as they were specified in the CREATE PROC statement. I think using the parameter names makes code easier to read. Also remember that character string are delimited by single quotes.

This procedure doesn't include any error handling yet. You can find a good (though advanced) discussion of that here. It also doesn't return anything to the client to indicate success or failure or what record was inserted. This article covers returning the new record's IDENTITY value. I didn't find a good article on return codes so maybe I'll write one some day. We also didn't cover transactions but I have a couple of links on them.

That should get you started and thanks for visiting the site.