How do you create stored procedure?

Answer 1

--Takes three arguments, the last one is optional

CREATE PROCEDURE sp1 (@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