ADONET FAQ - Sample Stored Procedures

How do stored procedure look like, can you provide some sample stored procedures?

Here we are presenting a brief review of four basic type of stored procedure for SELECT, INSERT, UPDATE and DELETE operations. In SQL Server, you can create and add stored procedures to your database using the SQL Server Enterprise Manager.

UPDATE Stored Procedure

A simple stored procedure to update a record is

CREATE PROCEDURE UpdateProc (
	@artId as int, 
	@title as varchar(100), 
	@topic as varchar(100),
	@authorId as int,
	@lines as int,
	@dateOfPublishing as datetime)
AS
	UPDATE Article SET
		title=@title, topic=@topic, authorId=@authorId,
		lines=@lines, dateOfPublishing=@dateOfPublishing 
	WHERE artId=@artId
GO

The name of stored procedure is UpdateProc and it has the input parameters for each of the fields of our Article table. The query to be executed when the stored procedure is run updates the record with the supplied primary key (@artId) using the supplied parameters. It is very similar to the code we have written to initialize command in the previous example and we hope you don’t have any problem in understanding this even you are not familiar with stored procedure.

INSERT Stored Procedure

A simple stored procedure to insert a record is

CREATE PROCEDURE InsertProc (
	@artId as int, 
	@title as varchar(100), 
	@topic as varchar(100),
	@authorId as int,
	@lines as int,
	@dateOfPublishing as datetime)
AS
	INSERT INTO article (artId, title, topic, authorId, lines, dateOfPublishing) 
	VALUES(@artId, @title, @topic, @authorId, @lines, @dateOfPublishing)
GO

The stored procedure above is named InsertProc and is very similar to the UpdateProc except that here we are using the INSERT SQL statement instead of the UPDATE command.

DELETE Stored Procedure

A simple stored procedure to delete a record is

CREATE PROCEDURE DeleteProc (@artId as int)
AS
	DELETE FROM article WHERE artId = @artId
GO

Here we have used only one parameter as to delete a record you only need its primary key value.

SELECT Stored Procedure

A simple stored procedure to delete a record is

CREATE PROCEDURE SelectProc 
AS
	SELECT * FROM Article
GO

This probably is the simplest of all. It does not take any parameter and only selects all the records from the Article table.

All the four stored procedures presented above are kept extremely simple so that the reader does not find any difficulty in understanding the use of stored procedure in his C#/VB.Net code. The real world stored procedures are much more complex and off course useful than these!

Back