Default Values
The Basics
In VB I just love optional parameters, it's a shame that C# doesn't have that feature(instead of overloading methods of different parameters.) In T-Sql we can simulate this optional parameters in our stored procedures by using default values:
PROC dbo.pr_GetInvoiceList (@PeriodMonth INT = 1, @PeriodYear INT = 2005)
AS
SELECT *
FROM InvoiceTable
WHERE PeriodMonth = @PeriodMonth AND PeriodYear =@PeriodYear
Then you can just execute your stored procedure sans the parameters:
EXEC pr_GetInvoiceList
The purpose of the default parameter values is to make sure our stored procedure will run even when there is no parameter assigned. Having said that however, the only time I find it is useful, is during testing.
An interesting application
I have used stored procs extensibly in my application, but I was kind of frustrated when a situation arises that not all parameters will be used for either an INSERT or UPDATE procedure. I use only a single stored proc to update for example any modification to our MedicalReports table. An update might contain just updating the last modified column or update both last modified column and approvedby column. I have to resort to client side updates to handle both scenario and find it cumbersome to maintain. Then I was toying the idea of default values, but instead of having actual values, I would instead default all the values to NULL.
PROC dbo.pr_UpdateMedRep(@RepId INT, @LastModBy VARCHAR(10)=NULL, @ApprovedBy VARCHAR(10)=NULL)
The body of our stored proc will be like this:
UPDATE MedicalReportsTable SET
LastModBy = ISNULL(@LastModBy, LastModBy),
ApprovedBy = ISNULL(@ApprovedBy, ApprovedBy)
WHERE MedicalReportsTable.RepId = @RepId
Now if I want to update only the LastModify column:
pr_UpDateMedRep 1, 'User1'
Updating only ApprovedBy column:
pr_UpDateMedRep 1, NULL, 'User2'
Without worrying that either column will be updated when it shouldn't be and I'm still using stored procedure to do so.