Stored Procedures For Beginners

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.

Database Script to Create Tables for Exercises

ALTER TABLE [dbo].[USERDETAILS] DROP CONSTRAINT FK_USERDETAILS_USERLIST

GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[USERDETAILS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USERDETAILS]

GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[USERLIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USERLIST]

GO

CREATE TABLE [dbo].[USERDETAILS] (
           [detail_id] [int] IDENTITY (1, 1) NOT NULL ,
           [usr_id] [int] NOT NULL ,
           [title] [varchar] (50) NULL ,
           [yrs_service] [numeric](18, 0) NULL ,
           [yrs_title] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[USERLIST] (

            [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
            [login] [varchar] (20) NOT NULL ,
            [pswd] [varchar] (20) NOT NULL ,
            [f_name] [varchar] (25) NULL ,
            [l_name] [varchar] (35) NOT NULL ,
            [address_1] [varchar] (30) NULL ,
            [address_2] [varchar] (30) NULL ,
            [city] [varchar] (30) NULL ,
            [state] [char] (2) NULL ,
            [zipcode] [char] (10) NULL ,            [email] [varchar] (50) NOT NULL

) ON [PRIMARY]

GO