Posted by beakersoft | Posted in SQL Server | Posted on 03-07-2007
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
if ISNULL (@year, 0) = 0
SELECT @month = DATENAME(month, GETDATE())
SELECT @year = DATENAME(year, GETDATE())
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!