SQL Server Stored Procedures – Fundamentals

Exercise:  Pass Data to a SELECT Stored Procedure

Create a stored procedure that returns one record, based on the table’s primary key. Remember to:

  1. Create the header record (commented)

  2. Create the stored procedure name and declare variables

  3. Create the rest of your stored procedure

When you are done, copy your stored procedure into the SQL Server New Stored Procedure window (if you are using a separate text editor), and check the syntax. Also, you may want to open the Query Analyzer and run the execute statement. I’ll provide both the stored procedure and execute statement (with sample data) below.

Answers

Stored Procedure:

/*
Name: usp_finduser
Description:  find a user
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_finduser

@usr_id int

AS

SELECT * FROM USERLIST
WHERE usr_id=@usr_id

Execute Statement:

exec usp_finduser ’1′

Did it work?  If not, keep trying!  You’ll get there.

In Closing

This has been a pretty aggressive lesson. You showed up somewhat familiar with databases, but probably knowing nothing about stored procedures (unless you are a database guru who read my article so you could viciously critique it later!). We have gone from defining stored procedures to writing them independently. That is great!  Stored procedures are an excellent way to insulate your programming logic from the threat of technology migrations in the future. They are useful, make for efficient application development, and are easy to maintain. Using the information and exercises above, you should be on your way to creating stored procedures to support any database-related endeavor.

Continues…



Array

No comments yet... Be the first to leave a reply!