multiple select statement in a stored-proc?

I am using SQL sever 2k and C#.

There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?

What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?

I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.

stored procedure example: NorthWind database:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @.@.ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

C# code example:

//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());

Thanks for your help.

reader.NextResult()

|||it works for SqlDataReader. however XmlTextReader seems don't have this method. anyway I can just use SqlDataReader.
thanks very much.

Multiple Select Parameters

Hello,
Am I correct when I conclude that in order to support multiple
selections at runtime for parameters I would need to create a custom
interface?
I do not want to reinvent the wheel if I don't have to.
Thank you in advance,
JerryThe simple way to do this is to have a text parameter and teach your users
to type in a comma delimited list. The problem is that they might not do it
correctly, and you can't prompt them with list of valid values..
Otherwise, you would have to do something special as you suggest...This is a
very common issue, and I hope one that is addressed in the next version...
--
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
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410111519.8fd28a4@.posting.google.com...
> Hello,
> Am I correct when I conclude that in order to support multiple
> selections at runtime for parameters I would need to create a custom
> interface?
> I do not want to reinvent the wheel if I don't have to.
> Thank you in advance,
> Jerry|||Hi
I have successfully setup 3 parameters that are entered at runtime.
To do this I setup each parameter as a dataset then tied them together via
the report parameters.
For each report parameter the values come from the query of the dataset for
that parameter.
Works a treat.
Fiona
"Jerry Nixon" wrote:
> Hello,
> Am I correct when I conclude that in order to support multiple
> selections at runtime for parameters I would need to create a custom
> interface?
> I do not want to reinvent the wheel if I don't have to.
> Thank you in advance,
> Jerry
>|||Hi
I have successfully use 3 parameters on a report via the following method.
1.
Setup a dataset for each report parameter. You need a table that has only
those values that are valid for that field (unique) ie like a dimension table.
2.
Then in â'report parametersâ' for each parameter say that its values are
obtained from the query of the dataset for that parameter.
Works well
Fiona
"Jerry Nixon" wrote:
> Hello,
> Am I correct when I conclude that in order to support multiple
> selections at runtime for parameters I would need to create a custom
> interface?
> I do not want to reinvent the wheel if I don't have to.
> Thank you in advance,
> Jerry
>|||It never occurred to me to have 3 parameters off of the same dataset...
Good job!
--
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
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eW7vUsFsEHA.2952@.TK2MSFTNGP10.phx.gbl...
> The simple way to do this is to have a text parameter and teach your users
> to type in a comma delimited list. The problem is that they might not do
it
> correctly, and you can't prompt them with list of valid values..
> Otherwise, you would have to do something special as you suggest...This is
a
> very common issue, and I hope one that is addressed in the next version...
> --
> 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
> "Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
> news:36f558cf.0410111519.8fd28a4@.posting.google.com...
> > Hello,
> >
> > Am I correct when I conclude that in order to support multiple
> > selections at runtime for parameters I would need to create a custom
> > interface?
> >
> > I do not want to reinvent the wheel if I don't have to.
> >
> > Thank you in advance,
> >
> > Jerry
>|||@.Wayne Snyder: Are you thinking we are using reporting services at home with
our childrens?
Teaching the user to enter manuel criteria is not qualified as a bad
workaround in 99% of real companies!
I guess such answers are only qualified to collect MVP points.
"Wayne Snyder" wrote:
> The simple way to do this is to have a text parameter and teach your users
> to type in a comma delimited list. The problem is that they might not do it
> correctly, and you can't prompt them with list of valid values..
> Otherwise, you would have to do something special as you suggest...This is a
> very common issue, and I hope one that is addressed in the next version...
> --
> 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
> "Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
> news:36f558cf.0410111519.8fd28a4@.posting.google.com...
> > Hello,
> >
> > Am I correct when I conclude that in order to support multiple
> > selections at runtime for parameters I would need to create a custom
> > interface?
> >
> > I do not want to reinvent the wheel if I don't have to.
> >
> > Thank you in advance,
> >
> > Jerry
>
>|||What about a list of 5000+ choices where the user has the liberty to
choose n items in the list. Items in the list are pharmaceutical names
that are quite long and prone to miss spelloing.
Wayne Snyder wrote:
> It never occurred to me to have 3 parameters off of the same
dataset...
> Good job!
> --
> 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
>|||Jerry,
I believe this will be available in SQL2005 RS, due sometime this year!
Chris
Jerry Nixon wrote:
> Hello,
> Am I correct when I conclude that in order to support multiple
> selections at runtime for parameters I would need to create a custom
> interface?
> I do not want to reinvent the wheel if I don't have to.
> Thank you in advance,
> Jerry|||We are arranging something that may be relevant, to support batch
printing of various items that may be created throughout the day (or
over several days, possibly).
We plan to have a Batch table and a BatchItem table - the first time a
user designates a form for printing it creates a Batch record and a
BatchItem record. Additional forms designated for the batch add
BatchItem records, with a Batch ID as a foreign key.
When the report is printed, the Batch ID is passed as a parameter and
the BatchItem records are used to build the Where clause in the stored
procedure that provides data for the report.
Obviously, the management of all this must be handled on the
application end - but it will support any number of choices, with only
one parameter value needing to be passed to the report.
Your Batch table should identify the report type, and whether and when
the batch was printed - the BatchItem child table can have fields of
different data types to collect parameter values of those data types -
you just need to reference the correct field in the stored procedure.
Our concern is somewhat with performance - this approach calls for a
subselect in the stored procedure, and where we want to use other
parameter sets for the same report, the use of dynamic SQL in the
stored procedure so we can make use of either the Batch ID (if it is
provided) or an alternate set of parameters.

multiple select in the parameter field in Crystal reports 8.5

I have a multiple select in the parameter field in Crystal reports 8.5 how do i check against that value in my SP?

Suppose I select 2 user from Drop down say

Mary
John

Are they comma seperated the way they r in Web based apps?
how do i build a SQL String based on the User criteria?Where is the dropdown - in Crystal (as a defined range of inputs) or in an application devloped from Vb or C++ or similar?
To use the VB example, when you populate a combo box, you can assign a value to the .ItemData property of the combo, and then return that to the report via the .ParameterFields collection exposed via the CR Active X control.
Say you populate your combo with data from a table:
RecordID Name
1 Fred
2 Mary
3 Joan
4 John
etc

then in VB code would be
...open recordset
...start looping thru records
cboTest.AddItem value_to_appear ' eg Fred
cboTest.ItemData(cboTest.NewIndex) = id 'eg 1
next

when you click on the combo, you can get the index and pass it to crystal
lngID = cboTest.ItemData(cboTest.ListIndex)

then in your code you can pass that to report when you open it, eg
Dim objPrintApp As CRAXDRT.Application
Dim objReport As CRAXDRT.Report
Dim objParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim objParamDef As CRAXDRT.ParameterFieldDefinition

' code presumes you have an input parameter to your report named pID
' Set the report source
Set objPrintApp = New CRAXDRT.Application
Set objReport = objPrintApp.OpenReport(strReportSourcePath & strSource)
objPrintApp.LogOnServerEx put logon parameters here

Set objParamDefs = objReport.ParameterFields
For Each objParamDef In objParamDefs
With objParamDef
Select Case .ParameterFieldName
'It finds and sets the appropriate Crystal parameters.
Case "pID"
.SetCurrentValue lngID

' add other Case conditions here to handle other parameter names.

' Catch-all for others
Case Else
strInput = InputBox("Enter a value for '" & .ParameterFieldName & "'")
If strInput <> "" Then
.SetCurrentValue strInput
Else
Exit Sub
End If
End Select
End With
Next

objReport.PrintOut

hope this helps

dave|||Thks for ur prompt reply...but the drop down is in crystal reports 8.5

I am using Crystal reports 8.5 stand alone with enterprise server......i mean i not using VB or anything else as App dev.
I have SP in SQL server 2000

I need to select multiple values from the drop down of the parameter field.
how do I put those values in the drop down of the parameter field which needs to be passed to the SP?

How do I build the Sql query once the multiple values r selected? are the multiple values comma seperated??

multiple select in one sp

I was wondering if it's possible to have a stored procedure that hastwo(or more) select statements which you can combine as a single result set.I am not able to use union as my select returns different number of columns.My selects are like this,This example is using only two table but i can have more then 2 tables in situation..

ELECT SUM(col1) AS sumcol1
FROM tbl1
WHERE (ID = @.para1')

SELECT SUM(col1) AS sumcol1, SUM(col2) AS sumcol2, SUM(col3)
AS sumcol3, SUM(col4) AS sumcol1
FROM tbl2
WHERE (id = @.para1).

thanks

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

|||

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

Both can be accessed from C#/VB, but they are separate result sets.

If a single result set is needed, then pad out the two queries with innocuous (sp?) dummy columns and union all them together.

|||

david wendelken:

Mikesdotnetting:

Sure you can. Each resultset will form a new DataTable in a DataSet, or if you are using a DataReader, you can access each resultset in turn by calling the DataReader.NextResult() method. Bear in mind that this won't work with a SqlDataSource control. It is only capable of filling one DataTable. I forget whether it keeps just the first resultset or the last one.

Technically speaking, the method you propose returns two separate result sets.

That's right. That's because I thought that was what the questioner wanted. Now I've re-read the original question, I can see it isn't.

Doh.

|||

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

|||

nb123:

Thanks David,i got what you are saying but how do you dummy out the columns?Sorry, for these lame questions...

cast(null as int) as dummy_int
cast(null as varchar(6)) as dummy_varchar6

or just use 0 or '' if zero or an empty space works better for you

|||

Thanks David,it works

Multiple select in a Store problem

Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
StefanoCould you try putting SET NOCOUNT ON at the beginning of the insert?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ste" <ste@.3civettesulcomoinews.it> wrote in message
news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano|||Thank you, that's ok
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ha scritto nel messaggio
news:eOp4aflHFHA.2620@.tk2msftngp13.phx.gbl...
> Could you try putting SET NOCOUNT ON at the beginning of the insert?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ste" <ste@.3civettesulcomoinews.it> wrote in message
> news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi, I have a problem...
> Supposed that in a store procedure, I have two operation for example:
> insert into mytable field1,field2 values ('bob','bobby')
> select max(id) as id_last from mytable
> go
> in this case I have returned a recordset of one record, of last record
> inserted, ok?
> With ODBC Connection this work fine, but if I use SQLOLEDB Connection
> like:
> Provider=SQLOLEDB;Data Source=172.16.1.102;Network
> Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
> return this error:
> ADODB.Recordset error '800a0cc1'
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
> I use SQL2000Server in a machine and ASP on IIS6 in another machine
> Why
> thank you in advance and sorry for my bad english
> Stefano
>
>

Multiple select in a Store problem

Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano
Could you try putting SET NOCOUNT ON at the beginning of the insert?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ste" <ste@.3civettesulcomoinews.it> wrote in message
news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
Hi, I have a problem...
Supposed that in a store procedure, I have two operation for example:
insert into mytable field1,field2 values ('bob','bobby')
select max(id) as id_last from mytable
go
in this case I have returned a recordset of one record, of last record
inserted, ok?
With ODBC Connection this work fine, but if I use SQLOLEDB Connection
like:
Provider=SQLOLEDB;Data Source=172.16.1.102;Network
Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
return this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.
I use SQL2000Server in a machine and ASP on IIS6 in another machine
Why
thank you in advance and sorry for my bad english
Stefano
|||Thank you, that's ok
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ha scritto nel messaggio
news:eOp4aflHFHA.2620@.tk2msftngp13.phx.gbl...
> Could you try putting SET NOCOUNT ON at the beginning of the insert?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ste" <ste@.3civettesulcomoinews.it> wrote in message
> news:%23mDuaOjHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi, I have a problem...
> Supposed that in a store procedure, I have two operation for example:
> insert into mytable field1,field2 values ('bob','bobby')
> select max(id) as id_last from mytable
> go
> in this case I have returned a recordset of one record, of last record
> inserted, ok?
> With ODBC Connection this work fine, but if I use SQLOLEDB Connection
> like:
> Provider=SQLOLEDB;Data Source=172.16.1.102;Network
> Library=DBMSSOCN;Database=mydata;User ID=abc;Password=123
> return this error:
> ADODB.Recordset error '800a0cc1'
> Item cannot be found in the collection corresponding to the requested name
> or ordinal.
> I use SQL2000Server in a machine and ASP on IIS6 in another machine
> Why
> thank you in advance and sorry for my bad english
> Stefano
>
>

Multiple select in 2005 Beta 2

I have heard that the ability to do multi-selects (selecting multiple records
in a drop down prompt) would be possible in the 2005 version of ssas. I have
the Beta 2 version and still do not think it is possible. Any help would be
greatly appreciated.Multi-value parameters are not supported in Beta 2.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"chicagoclone" <chicagoclone@.discussions.microsoft.com> wrote in message
news:10D5F583-946B-4A2D-8079-8F711114B2C2@.microsoft.com...
>I have heard that the ability to do multi-selects (selecting multiple
>records
> in a drop down prompt) would be possible in the 2005 version of ssas. I
> have
> the Beta 2 version and still do not think it is possible. Any help would
> be
> greatly appreciated.|||Is this something that is PLANNED for the general release?
"Rajeev Karunakaran [MSFT]" wrote:
> Multi-value parameters are not supported in Beta 2.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "chicagoclone" <chicagoclone@.discussions.microsoft.com> wrote in message
> news:10D5F583-946B-4A2D-8079-8F711114B2C2@.microsoft.com...
> >I have heard that the ability to do multi-selects (selecting multiple
> >records
> > in a drop down prompt) would be possible in the 2005 version of ssas. I
> > have
> > the Beta 2 version and still do not think it is possible. Any help would
> > be
> > greatly appreciated.
>
>