This topic can be an ongoing argument between the parties who swear by Stored Procedures (SP) and the parties that absolutely apose them. Each perspective make valid points, but it is entirely up to you to decide. If you love them that is great and if you hate them that is great too.
I have only been using (SPs) for a short time so I cannot complain, but then again I am not an expert. I do find that (SPs) help me a lot with my daily support. So let's run through an example of creating a simple (SP) that returns a simple result set. Then we will create a slightly more advanced (SP) that requires a variable in order to return a specific result set. Again these are simple examples!
Let's just say you have a table called "tbl_Contacts" and in it you have firstName, LastName, DOB, a computed column called AGE and a IsDeleted column with a BIT data type (0 being active and 1 being deleted).
CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName, lastName, CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0) --Returns all active records
To test your freshly created (SP) run the following:
EXEC sp_GetAllActiveContacts
If you do not have a table called tbl_Contacts don't worry just use the examples I posted below!
-- [ CREATE TABLE ]
CREATE TABLE [dbo].[tbl_Contacts]
(
[ContactID] [int] NOT NULL,
[firstName] [nvarchar](50) NOT NULL,
[lastName] [nvarchar](50) NOT NULL,
[IsDeleted] [bit] NULL CONSTRAINT [DF_tbl_Contacts_conDeleted] DEFAULT ((0)),
[createDate] [datetime] NULL CONSTRAINT [DF_tbl_Contacts_conCreateDate] DEFAULT (getdate()),
[DOB] [smalldatetime] NULL,
[Age] AS (datediff(year,[DOB],getdate()))
)
-- [ CREATE RECORDS ]
INSERT INTO tbl_Contacts
VALUES (1,'Adam','Sandler',DEFAULT,DEFAULT,'9-9-1966')
INSERT INTO tbl_Contacts
VALUES (2,'Emmanuelle','Chriqui',DEFAULT,DEFAULT,'12-10-1977')
INSERT INTO tbl_Contacts
VALUES (3,'Rob','Schneider',DEFAULT,DEFAULT,'10-31-1963')
INSERT INTO tbl_Contacts
VALUES (4,'Val','Kilmer',DEFAULT,DEFAULT,'12-31-1959')
INSERT INTO tbl_Contacts
VALUES (5,'Megan','Fox',DEFAULT,DEFAULT,'5-16-1986')
-- [ VERIFY RECORDS ]
SELECT * FROM tbl_Contacts
-- [ CREATE STORED PROCEDURE ]
CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0)
-- [ TEST STORED PROCEDURE ]
EXEC sp_GetAllActiveContacts
-- [ CREATE STORED PROCEDURE WITH VARIABLE ]
CREATE PROC sp_GetAllActiveContactsByID ( @ContactID INT )
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (ContactID = @ContactID) AND (IsDeleted = 0)
-- [ TEST STORED PROCEDURE ]
DECLARE @ID INT
SET @ID = 1
WHILE @ID <= 5
BEGIN
EXEC sp_GetAllActiveContactsByID @ID
SET @ID = @ID +1
END
