Tuesday, July 03, 2007

Passing Optional Parameters To Stored Procedure

Found quite a cool thing recently, that means you can have optional parameters in a SQL stored procedure.
This means you can pass the options into the procedure if you have some, or if none are passed get the SP to use some defaults instead. It took a while to get the syntax correct, but its fairly straight forward, in this example we will use the year and month as optional parameters:

@year int = NULL, @month char(20) = NULL
AS

if ISNULL (@year, 0) = 0
begin
SELECT @month = DATENAME(month, GETDATE())
SELECT @year = DATENAME(year, GETDATE())
end

First of all we declare the 2 variables, then we check if the first one is NULL (nothing passed). If it is then we set both the month and year to be the current month and year. Simple but effective!

I have another article published at aspfree.com, this one is about creating a simple RSS feed using ASP.net, written in c#. Read it HERE

No comments: