How do you write a stored procedure?

Answer 1

SQL Server is a great database, and you can get a free developer edition from Microsoft. Stored procedures are a good way to develop your application as they provide several key benefits over inline SQL.

* Precompiled execution plans make for faster data access.

* Use of parameters means less chance for sql injection vulnerabilities.

* Centralised location of data access code means more re-use opportunity.

* Procedures can be easily shared by different applications and languages using the same data store.

This can be used as a pretty generic template for a stored procedure. It always ensures that the old version is removed before creating the new one, and it shows some examples of using SQL Server functions to do variable manipulation: --Remove any existing versions of procedure

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = N'sp_GetArticle'

AND type = 'P')

DROP PROCEDURE sp_GetArticle

GO

--Takes three arguments, the last one is optional

CREATE PROCEDURE sp_GetArticle (@AuthorId int, @DateFrom DateTime, @DateTo DateTime = NULL)

AS

BEGIN

--strip any time portion from the date passed in

SELECT @DateFrom = Convert(datetime,convert(char(10),@DateFrom,101))

--if no 'dateto' provided, then default to 23 hours, 59 minutes and 59 seconds

--this is because dates are stored by default as 00:00:00, and

--we only want to get data for one day

IF @DateTo IS NULL

BEGIN

SET @DateTo = DATEADD ( second , -1, @DateFrom + 1 )

END

SELECT a.Name,

a.Body,

t.Description,

u.Name

FROM Article a

INNER JOIN Topic t on a.TopicId = t.Id

LEFT OUTER JOIN Users u on u.Id = a.AuthorId

WHERE a.AuthorId = @AuthorId

AND a.CreatedDate >= @DateFrom

AND e.CreatedDate <= @DateTo

END

GO