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

iif/switch statement does not work in input parameter expression

Hi There,

One of the parameters that i need to pass to a stored procedure of my
report, i wish to dynamically calculate from another input default parameter

lets say parameter 'DrillDown' = "ABCDEFG"

paramenter 'MyLevel' is calculated by either of the following a switch or
iif statement:

=switch
(left(Parameters!DrillDown.Value,1)="A",1,
left(Parameters!DrillDown.Value,1)="B",2,
left(Parameters!DrillDown.Value,1)="C",3,
left(Parameters!DrillDown.Value,1)="D",4,
left(Parameters!DrillDown.Value,1)="E",5,
left(Parameters!DrillDown.Value,1)="F",6,
left(Parameters!DrillDown.Value,1)="G",7)
--or--
=iif(left(Parameters!RepLvl.Value,1) = "A",1,
iif(left(Parameters!RepLvl.Value,1) = "B",2,5))

Trouble is none of the 2 above work!

i get the following error: The value expression for the report parameter
‘MyLevel’ contains an error: [BC30201] Expression expected.

Wierd thing is if i just have '=iif(left(Parameters!RepLvl.Value,1) =
"A",1,5)' without an 'else if' it works fine !

Can any one help please!

Cheers

Dave

why not use custom code?

I have found for complicated IIF statements, it's easier and safer to create a custom code function.

|||

I would use a code function but for input parameters on start of a report you cant assign them to a 'code.' parameter. Can you? I get an error when i try this.

I got it to work , by taking out all the spaces ?!?!?

Wierdly it works with the spaces in if i have the expression elsewhere in the report, for intsance in a text box .

Anyhow thanks for the reply

Cheers

Dave

IIF Function in Dataset

I am trying to build my dataset WHERE clause with the IIF function.
I have 4 parameters that include a "ALL" option and that is what I am trying
to cater for.
Have done is successfully when there is only one parameter involved,
IIF (Parameters!Rep.Value = "0", " WHERE A.Region = '" &
Parameters!Region.Value & "'" , " WHERE A.Region = '" &
Parameters!Region.Value & "' AND A.SLPRSNID = '" & Parameters!Rep.Value &
"'")
but now I have multiple conditions ... have tried the following ...
" WHERE (CustomerServiceRatesReport.Region = @.Region) " &
IIF (Parameters!Rep.Value = "0", "" , " AND
CustomerServiceRatesReport.SLPRSNID = '" & Parameters!Rep.Value & "'") &
IIF (Parameters!Depot.Value = "0", "" , " AND
CustomerServiceRatesReport.OFFID = '" & Parameters!Depot.Value & "'") &
IIF (Parameters!ContractType.Value = "0", "" , " AND
CustomerServiceRatesReport.AV_Contract_Type = '" &
Parameters!ContractType.Value & "'") &
IIF (Parameters!CustomerNumber.Value = "0", "" , " AND
CustomerServiceRatesReport.CUSTNMBR = '" & Parameters!CustomerNumber.Value &
"'")
The above does not give me any errors, but neither does it give me any
results on the report - no matter what my parameter selections are.
Any help / pointers or suggestions on how I can make this work or
alternatives would be much appreciated.
Thank you,
SmeSme,
One thing that I do in these situations is place a textbox on my report
that contains the expression in your dataset, that way you can see what
SQL the dataset is executing.
Should give you a clue about what the query syntax ends up being after
all the expressions are evaluated.
Andy Potter|||Hi Andy,
Thank you for your reply. I will most certainly try that.
Is there any error in the syntax though?
What would the basic syntax be for a multiple IIF function in a query?
Kind Regards,
Sme|||Just in case someone needs the solution ... quite simple actually ...
WHERE (CustomerServiceRatesReport.Region = @.Region) AND
ISNULL(RTRIM(CustomerServiceRatesReport.SLPRSNID ),'') =COALESCE(@.Rep,RTRIM(CustomerServiceRatesReport.SLPRSNID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.OFFID),'') =COALESCE(@.Depot,RTRIM(CustomerServiceRatesReport.OFFID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'') =COALESCE(@.ContractType,
RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.CUSTNMBR),'') =COALESCE(@.CustomerNumber, RTRIM(CustomerServiceRatesReport.CUSTNMBR),'')

IIF and multi-valued parameters

Hi

i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.

basically, the report gets loads of data back from DataCube.

if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.

but

i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart

so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)

am doing this by trying to get the selected value or "xxxxx" if noting selected

to hide the second chart when only one category has been selected, tried

e.g. for the third chart

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

Parameters!LOOKUPITEMCategoryDescription.Label(2),

"xxxxx")

doesnt work - gives an #Error as a reult

But it works fine when there are > 2 categories selected

also

this works fine when there is 2 or >2 category selected

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

"more than 2",

"not more than 2")

it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!

help please

thx

IIf evaluates everything in the expression and does not employ shortcutting. This means the expression Parameters!LOOKUPITEMCategoryDescription.Label(2) will cause a problem if there are less than two items selected, because it is evaluated regardless. This means that you will need to change the way you check if a parameter option has been selected, maybe by doing an InStr() on Parameters!LOOKUPITEMCategoryDescription, or by calling a function with the parameter and looping safely through it in the function.

Also, i would think it is better to hide you chart with an expression in the Visibility|Hidden property rather than trying to use the Filters property.

|||

You have to use your report code to achieve this. Have a global variable of type object array in your code and write a function in the code which will receive an object array and store it in the global variable. Then have an invisible textbox at the top of your report and call this function from the textbox expression like Code.YourFunction(Parameters!LOOKUPITEMCategoryDescription.Value).

Then write another function in the code which will receive the index and check if the count of the items in the object array is greater than the received index and return the appropriate item from the object array. Call this function in your Filter expression of the chart.

If you need example about how to do it, please let me know.

Shyam

|||

sluugy & Shyam

thx for this - should have posted an answer as i had already realised about vb evaluating the whole expression in IIF and written the function as described

thx

m

Ignory all value if marked "Select-All" in reports

Hello!

I have SQL query in my report, using multi-value parameters:

Select Table1.Item

From Table1

Where Table1.Item in(@.item)

Multi-value parameters “item” have properties “Available values” From-query and returned ~200 values. I want, If I selected in my reports “Select-All” then SQL query ignore “Where Table1.Item in(@.item)” How?

Sorry my bad English :-)

Thanks!

Try using Where (@.item is null or table1.item in (@.item) or the COALESCE function.