Immutability of procedure parameters

So, I was having just the most frustrating bug a few months ago, and my
boss says, "Oh, yeah, you should never assign a new value to an IN
parameter in SQL Server."
He couldn't give me a reference -- I'm happy enough not to do it, and
it solved that problem, but I really can't suggest to _other people
that they copy values and so forth unless I can point to some
documentation, which I've been unable to find. Is it anywhere written
that "assigning new values to IN parameters in SQL Server stored
procedures may product unpredictable results?"
Thanks!
IonIon
Do you mean that you get different data back, or the performance is
different.
Obviously, if you have a procedure that finds customers given their id
number, and you call the procedure with customer ID 112 and then the
procedure changes that value to 220 before searching, you'll get results for
different customer than what you were expecting.
I've also found cases of performance problems because of this, because the
proc is optimized based on the value passed IN, and then if you change it to
another value, it still uses the original plan based on the original value.
You should keep parameters as they were, and if you need to modify them,
assign the new value to a local variable, so it is clear what values are
parameters and which are variables. They are treated very differently.
HTH
Kalen Delaney, SQL Server MVP
<ionFreeman@.gmail.com> wrote in message
news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...
> So, I was having just the most frustrating bug a few months ago, and my
> boss says, "Oh, yeah, you should never assign a new value to an IN
> parameter in SQL Server."
> He couldn't give me a reference -- I'm happy enough not to do it, and
> it solved that problem, but I really can't suggest to _other people
> that they copy values and so forth unless I can point to some
> documentation, which I've been unable to find. Is it anywhere written
> that "assigning new values to IN parameters in SQL Server stored
> procedures may product unpredictable results?"
> Thanks!
> Ion
>|||Kalen,
Thanks for responding. The issue that I was seeing was that if I
modified a passed date parameter directly, the stored procedure never
came back -- the connection wasn't closed and no data was returned. The
calling application indicates that it wished to cover a maximal date
range by passing identical start and end times, and if I tried to
implement that logic by modifying the passed dates themselves, I lost
contact with the process -- it was really pretty mysterious.
It's possible this plan business is the ticket -- the proc expected
to return no data (based on its infinitesimal date range), but ended up
with all of it.
Thanks a bunch! Poking around in TechNet, I found
http://www.microsoft.com/technet/pr...comp.mspx#EDUAE
which asserts your point.
Ion
Kalen Delaney wrote:
> Ion
> Do you mean that you get different data back, or the performance is
> different.
> Obviously, if you have a procedure that finds customers given their id
> number, and you call the procedure with customer ID 112 and then the
> procedure changes that value to 220 before searching, you'll get results f
or
> different customer than what you were expecting.
> I've also found cases of performance problems because of this, because the
> proc is optimized based on the value passed IN, and then if you change it
to
> another value, it still uses the original plan based on the original value
.
> You should keep parameters as they were, and if you need to modify them,
> assign the new value to a local variable, so it is clear what values are
> parameters and which are variables. They are treated very differently.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <ionFreeman@.gmail.com> wrote in message
> news:1150834879.252067.128120@.h76g2000cwa.googlegroups.com...sql