Multi-value parameter returns syntax error

I've created a report that includes a multi-value parameter, however when
more than one value is seelcted from the list the report produces an error
that references "Incorrect Syntax near ",".
How do I correct this?Show how you are using the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> I've created a report that includes a multi-value parameter, however when
> more than one value is seelcted from the list the report produces an error
> that references "Incorrect Syntax near ",".
> How do I correct this?
>|||Send the Query where you are using this parametre.For Multivalue you
should use "in" keyword instead of "="
hope this might be your problem
Bruce L-C [MVP] wrote:
> Show how you are using the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "StMaas" <StMaas@.discussions.microsoft.com> wrote in message
> news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> > I've created a report that includes a multi-value parameter, however when
> > more than one value is seelcted from the list the report produces an error
> > that references "Incorrect Syntax near ",".
> > How do I correct this?
> >sql

multiply by 1/4

Hi,
What's wrong with this:
DECLARE @.a as float
DECLARE @.b as float
SET @.a = (0.25) * 100
SET @.b = (1/4) * 100
print @.a
print @.b
Returns:
25
0
Why is the following returning 0 and not 25?
SET @.b = (1/4) * 100
Something similar is coded somewhere in one of our apps and is causing
incorrect reporting.
Thanks!gracie wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!
Do:
SET @.b = (1/4.0) * 100 ;
Otherwise you get an integer division, which equals 0.
Better still, specify a datatype for numeric literals:
SET @.b = (CAST(1 AS FLOAT)/CAST(4 AS FLOAT)) * 100 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Perfect. Thanks.
Since they both return the correct result, why is specifying the datatype
for numeric literals better?
"gracie" wrote:
> Hi,
> What's wrong with this:
> DECLARE @.a as float
> DECLARE @.b as float
> SET @.a = (0.25) * 100
> SET @.b = (1/4) * 100
> print @.a
> print @.b
> Returns:
> 25
> 0
> Why is the following returning 0 and not 25?
> SET @.b = (1/4) * 100
> Something similar is coded somewhere in one of our apps and is causing
> incorrect reporting.
> Thanks!|||It gives you control of the input datatype so you can determine over which datatypes the operation
will occur.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:D037A0AB-7A95-4285-9471-3695FAB55CFC@.microsoft.com...
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
> "gracie" wrote:
>> Hi,
>> What's wrong with this:
>> DECLARE @.a as float
>> DECLARE @.b as float
>> SET @.a = (0.25) * 100
>> SET @.b = (1/4) * 100
>> print @.a
>> print @.b
>> Returns:
>> 25
>> 0
>> Why is the following returning 0 and not 25?
>> SET @.b = (1/4) * 100
>> Something similar is coded somewhere in one of our apps and is causing
>> incorrect reporting.
>> Thanks!|||gracie wrote:
> Perfect. Thanks.
> Since they both return the correct result, why is specifying the datatype
> for numeric literals better?
>
Otherwise, how can you guarantee which numeric datatype, scale and
precision the server will pick? Not specifying the datatype may work
the way you expect it today, but the next version of SQL Server may
change things. In fact, the rules for implict casting of datatypes have
changed several times in different releases of SQL Server.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Multiple tables in the dataset

Hi,
I have a stored proc which returns multiple result sets. I think SSRS picks
up the first one by design. What's the best way forward, a data extension, or
is there a simpler way?
Thanks in advance,
Regards,
DattaYou need to split each result set into a uniqe dataset in RS. It can't
handle multiple result sets. Can you create new stored procedures from the
queries in the original one?
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> Hi,
> I have a stored proc which returns multiple result sets. I think SSRS
> picks
> up the first one by design. What's the best way forward, a data extension,
> or
> is there a simpler way?
> Thanks in advance,
> Regards,
> Datta|||Thanks, I suspected so.
I can split the procedures, but I think a custom data extension is the right
way to go, if there is no in-built support.
"Kaisa M. Lindahl Lervik" wrote:
> You need to split each result set into a uniqe dataset in RS. It can't
> handle multiple result sets. Can you create new stored procedures from the
> queries in the original one?
> Kaisa M. Lindahl Lervik
> "Datta" <Datta@.discussions.microsoft.com> wrote in message
> news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> > Hi,
> >
> > I have a stored proc which returns multiple result sets. I think SSRS
> > picks
> > up the first one by design. What's the best way forward, a data extension,
> > or
> > is there a simpler way?
> >
> > Thanks in advance,
> > Regards,
> > Datta
>
>

Multiple SP values overriding the desired SP value

I have a stored procedure (lets call him Earl to keep things clear) that executes another sp (lets call him Daryl). Daryl returns a value, and although the last thing the first Earl does is selects a record set to return to the code, the only thing the code can access is values from Daryl. I am using NOCOUNT ON in the beginning of Earl, and NOCOUNT OFF at the end of Earl, but I still get Daryl's values.

I even put NOCOUNT ON / OFF in Daryl, but no such luck. How do I bypass Daryl's values and get to Earl's?

Thanks.

Could you post your code?|||

NOCOUNT only effects the rowcount (as in the message (n row(s) affected), and the global @.@.rowcount) not the actual returning of values.

So, are you saying the rowcount is being updated even with SET NOCOUNT ON ?

|||

no, nothing to do with the row count I guess.

What it is: because the secondary SP, which is called by the first, uses a select statement, that data generated from the select is being returned from the primary SP, and not the intended data.

Here is a boiled down version of the SP:

alter procedure earl
as
begin

insert into table (x, y, z) values ('a', 1, 'c')
@.id = @.@.identity

exec daryl(@.id )

select * from table where id = @.id

end

alter daryl
@.id int
as
begin
insert into joiningtable (x, y, z) values (@.id, 1, 'c')

@.error = @.@.error

if(@.error > 0)
select @.error as result
else
select @.@.identity as result

end

so the output from Earl is @.@.identity from Daryl, and not "select * from table where id = @.id " from Earl.

Daryl needs to return data, because this SP can be called directly from another function if @.id is known.

|||Earl will return the intended records in the second result set. So you can try to reach the Next resultset.|||

Yes, this will return two result sets.

What are you using on the client side to recieve these results? If try this from sqlcmd\osql\SSMS, you will see both result sets.

Multiple return

Hi,
I have one stored procedure with multiple selects, like this:
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
Now it returns nothing, I suspected the count values.
I tried it with return... but then I only get one value.
How can I get the values as a table? With NumUsers and NumPages as
columnnames?
Thanks!hi,
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
SELECT @.NumUsers As NumUsers, @.NumPages As NumPages
"Arjen" <boah123@.hotmail.com> wrote in message
news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
> Hi,
> I have one stored procedure with multiple selects, like this:
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> Now it returns nothing, I suspected the count values.
> I tried it with return... but then I only get one value.
> How can I get the values as a table? With NumUsers and NumPages as
> columnnames?
> Thanks!
>|||Simple... ;-)
Thanks!
"arik" <arikf@.top4.com> schreef in bericht
news:%23qLX56YoFHA.2080@.TK2MSFTNGP14.phx.gbl...
> hi,
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> SELECT @.NumUsers As NumUsers, @.NumPages As NumPages
>
> "Arjen" <boah123@.hotmail.com> wrote in message
> news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
>|||Hi
It would be better to return them as output parameters as there will be less
overhead in doing so. See the topic "Returning Data Using OUTPUT Parameters"
in Books online.
John
"Arjen" wrote:

> Simple... ;-)
> Thanks!
>
>
> "arik" <arikf@.top4.com> schreef in bericht
> news:%23qLX56YoFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
>|||You can use OUTPUT parameters or a simple resultset.
CREATE PROCEDURE dbo.foo1
AS
BEGIN
DECLARE @.NumUsers INT, @.NumPages INT
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
SELECT NumUsers = @.NumUsers, NumPages = @.NumPages
END
GO
CREATE PROCEDURE dbo.foo2
AS
@.numPages INT OUTPUT,
@.numUsers INT OUTPUT
BEGIN
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
END
GO
Using the resultset is more code in the procedure (and more expensive
resource-wise) but simpler to deal with in the caller. Using Output
parameters makes the procedure itself smaller and lighter, but is a bit more
cumbersome to code from the caller.
A
"Arjen" <boah123@.hotmail.com> wrote in message
news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
> Hi,
> I have one stored procedure with multiple selects, like this:
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> Now it returns nothing, I suspected the count values.
> I tried it with return... but then I only get one value.
> How can I get the values as a table? With NumUsers and NumPages as
> columnnames?
> Thanks!
>

Multiple Resultsets in DPE

Hi,
I'm writing a DPE, which returns a multiple resultsets(datatables) from a
extenal data source. I would like to map these to dataregions in the report.
How do i do this?
Or To make it simple, let us assume i have a stored procedure returning
multiple resultsets and how do i map these result sets to
dataregions(Table,List etc.,)
thanks
shankarYou cannot return multiple rowsets from a single query in the current
version.
You would need to break this into multiple queries, each of which returns a
single rowset.
In your case, you could add a parameter to the stored procedure to indicate
which rowset to return. Then have multiple data sets, each of which calls
the stored procedure with a different value for that parameter.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uGNAmgzcEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> I'm writing a DPE, which returns a multiple resultsets(datatables) from a
> extenal data source. I would like to map these to dataregions in the
report.
> How do i do this?
> Or To make it simple, let us assume i have a stored procedure returning
> multiple resultsets and how do i map these result sets to
> dataregions(Table,List etc.,)
> thanks
> shankar
>|||SQL Server 2000 Reporting Services only supports one dataset pre data region
unless
the other data regions references are aggregated
(=First("Field!<Fieldname>.Value, "<DataRegionName>")>.
You will need to join your results in the query.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uGNAmgzcEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> I'm writing a DPE, which returns a multiple resultsets(datatables) from a
> extenal data source. I would like to map these to dataregions in the
report.
> How do i do this?
> Or To make it simple, let us assume i have a stored procedure returning
> multiple resultsets and how do i map these result sets to
> dataregions(Table,List etc.,)
> thanks
> shankar
>

Multiple ResultSets

Hi
I have a stored procedure which returns mulitple resultsets
rs = call.executeQuery();
call.getMoreResults();
irs = call.getResultSet();
I want to pass these two resultsets to another method.
When i pass these two
i get error saying "ResultSet is closed" for the first resultset.
i came to know there is method in jdk1.4.2 which keeps the resultsets open.
"call.getMoreResults(Statement.Keep_Current_Result )"
But i tried to use that But again i have another problem
Im using WSAD5.1 which uses to jdk1.4.1
So i configured the setting in WSAD so that it uses jdk1.4.2.
So at compile time im not gettign any problem
But at runtime it says
"getMoreResults(int) is not supported by webshpere java.sql.Statement
Implementation"
Can you please help me regarding this..
Thanks in Advance
jaya nair wrote:

> Hi
> I have a stored procedure which returns mulitple resultsets
> rs = call.executeQuery();
> call.getMoreResults();
> irs = call.getResultSet();
> I want to pass these two resultsets to another method.
> When i pass these two
> i get error saying "ResultSet is closed" for the first resultset.
> i came to know there is method in jdk1.4.2 which keeps the resultsets open.
> "call.getMoreResults(Statement.Keep_Current_Result )"
> But i tried to use that But again i have another problem
> Im using WSAD5.1 which uses to jdk1.4.1
> So i configured the setting in WSAD so that it uses jdk1.4.2.
> So at compile time im not gettign any problem
> But at runtime it says
> "getMoreResults(int) is not supported by webshpere java.sql.Statement
> Implementation"
> Can you please help me regarding this..
> Thanks in Advance
Hi. By JDBC spec, getMoreResults() will close any previous
result set. You have to do whatever processing you want
with a given resultset before getting another from the same
statement.
Joe Weinstein at BEA Systems
|||Hi Joe,
I have a problem again while retriveing multiple results sets from SQLServer
stored procedure
I used call.execute(), then tried to retrive OUT parameters using
call.getString(1) , it works fine
later im trying to retrive multiple resultsets one after other.
As you said first i tried to retrieve first resultset using
rs1 = call.getResultSet();
and tried to retrieve second one
call.getMoreResults()
rs2 = call.getResultSet()
But im getting rs1 and rs1 are null. Im not getting resultsets
But when i execute this stored proc using Query Analyser. It returns both
resultsets and i can see the records.
But using jdbc im not able to retrieve.
Can you tell me why?
thanks in advance
sudha
"Joe Weinstein" wrote:

>
> jaya nair wrote:
>
> Hi. By JDBC spec, getMoreResults() will close any previous
> result set. You have to do whatever processing you want
> with a given resultset before getting another from the same
> statement.
> Joe Weinstein at BEA Systems
>

multiple resultset in the report

I have a stored procedure which returns multiplr resultsets ,and I'd like to
show it in different tabls in my report.Is it doable?
Thanks for your help.
RayRay, No, seperate result sets cannot be accessed within one dataset.
RS will always use the first resultset. You will either have to merge
the results into one set or create a split version of the SP, one SP
producing one set another SP to produce the other and use them in
seperate datasets.
Chris
ALI-R wrote:
> I have a stored procedure which returns multiplr resultsets ,and I'd
> like to show it in different tabls in my report.Is it doable?
> Thanks for your help.
> Ray