Skip Navigation LinksHome > Categories > Code from a Category

Stored Procedures: Parameters, Inserts and Updates



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 951
Add Date: 12/22/2013
In this article I'll cover how to pass a parameter into a stored procedure and doing some simple inserts and updates.

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:

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::

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:

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:
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:

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.