Stored Procedures: Parameters, Inserts and Updates

By Bill Graziano on 26 February 2001 | 2 Comments | 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.

Discuss this article: 2 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

An Evaluation of Stored Procedures for the .NET Developer (22 March 2004)

Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts

Can i order by on bulk insert? (0 Replies)

Challenge:Max Concurrent CalRecordings Per month (1 Reply)

how to convet sql server db file to mdf (7 Replies)

Free SQL Database Tool Repair (9 Replies)

Error: 9003, Severity: 20, State: 1. (3 Replies)

SQL Error 7320.......Using linked servers (34 Replies)

Need help for query (0 Replies)

Create Table and update automati (0 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -