I dunno if I mentioned before that I changed some of the SPROCs in the company databases to accept multiple cultures (this is, retrieve values in multiple languages from the new tables). The main problem is that the company already has more than 10 applications working with those same databases, so the new changes in the SPROCs had to run smoothly without breaking any other application.
I became paranoid, heh, well, close to it.
To day I was veryfying one of the oldest web apps and noticed that one of the dropdownlists that gets the data from the SPROC Q_Regions was blank, gosh!
I promptly opened my Query Analyzer to try figure out what was going on and run this two scripts:
DECLARE @RC int
DECLARE @regionlist varchar(100)
DECLARE @minlevel int
DECLARE @maxlevel int
DECLARE @sitelist varchar(25)
DECLARE @industry char(1)
DECLARE @culture char(5)
— Set parameter values
EXEC @RC = [IRONSearch].[dbo].[Q_Regions] @regionlist, @minlevel, @maxlevel, @sitelist, @industry, @culture
and the script setting the params:
DECLARE @RC int
DECLARE @regionlist varchar(100)
DECLARE @minlevel int
DECLARE @maxlevel int
DECLARE @sitelist varchar(25)
DECLARE @industry char(1)
DECLARE @culture char(5)
— Set parameter values
set @minlevel=10
set @maxlevel=30
set @sitelist=’1′
set @culture=’en-US’
EXEC @RC = [IRONsearch].[dbo].[Q_Regions] @regionlist, @minlevel, @maxlevel, @sitelist, @industry, @culture
I was expenting the same results. The SPROC has default values that should be used if the parameter is null…
The results differ big time, so I was, hrmmm, why the default values are not taken at all?
Anyways, making the story short, if the SPROC is called
EXEC @RC = [IRONsearch].[dbo].[Q_Regions]
without any parameter at all, the default values are taken but if the SPROC is called with the parameters = null as in the first case, the default values are not taken as SQL Server takes the value null for each of them.
It’s good to know this gotcha so I can write the Q_Regions with more robust code, checking for nulls and not let my data layer in the app alone with this.
The devil is in the details, isn’t it? 😉