Calling a SPROC gotcha
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? ;)
0 Comments:
Post a Comment
<< Home