Parameter Limitations?

Hi all,
I am calling a SP from RS inwhich there is an optional parameter of string
type. I allow the parameter to be blank and the results returned are
correct. When i try to filter my results by adding a name (ie Tic-Tot's
Daycare) and run the preview again the results are the same as if i left the
parameter blank. When i run the SP in the data view and include the name it
returns the filtered results. When i run the SP in QA i get the filtered
result. My only thought is the odd chars in the string ( single quote and
hyphen) could be messing up the parameter passing. Can some one please
confirm this or give a different suggestion for this issue.
Thanks in advance,
SPOSince the string delimiter in SQL is a single quote, I would suspect that is
your problem... You might try to double up on single quotes in your
parameters ,( which escapes the quote in SQL)... First eliminate the quote
from the parameter and see if it works...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"iamspo" <iamspo@.discussions.microsoft.com> wrote in message
news:5097986A-9711-4802-B839-9BB7A8C22F49@.microsoft.com...
> Hi all,
> I am calling a SP from RS inwhich there is an optional parameter of string
> type. I allow the parameter to be blank and the results returned are
> correct. When i try to filter my results by adding a name (ie Tic-Tot's
> Daycare) and run the preview again the results are the same as if i left
> the
> parameter blank. When i run the SP in the data view and include the name
> it
> returns the filtered results. When i run the SP in QA i get the filtered
> result. My only thought is the odd chars in the string ( single quote and
> hyphen) could be messing up the parameter passing. Can some one please
> confirm this or give a different suggestion for this issue.
> Thanks in advance,
> SPO|||I actually found the error. Since i added the name filter parameter as a
second version to this SP it was not picked up by RS. I went in and added
the parameter (report => report parameters...) but never went back and added
it to the parameter list in the data set. Once i did think the data set knew
which parameter to associate this value to and sent it to the SP. The result
returned were correct, none of the special char's used caused an issue.
Thanks for the help!!!
"iamspo" wrote:
> Hi all,
> I am calling a SP from RS inwhich there is an optional parameter of string
> type. I allow the parameter to be blank and the results returned are
> correct. When i try to filter my results by adding a name (ie Tic-Tot's
> Daycare) and run the preview again the results are the same as if i left the
> parameter blank. When i run the SP in the data view and include the name it
> returns the filtered results. When i run the SP in QA i get the filtered
> result. My only thought is the odd chars in the string ( single quote and
> hyphen) could be messing up the parameter passing. Can some one please
> confirm this or give a different suggestion for this issue.
> Thanks in advance,
> SPO