Multiple Values Returned from Stored Proc

I need to return 2 values from a stroed proc. Can you have more than one
output parameters in a stored proc? If not, would bringing back a dataset b
e
better than two round trips to the database to get the 2 values I need?
--
Robert HillRobert wrote:
> I need to return 2 values from a stroed proc. Can you have more than
> one output parameters in a stored proc? If not, would bringing back
> a dataset be better than two round trips to the database to get the 2
> values I need?
Output variables are generally faster to return than generating a 1 row
result set. You can have more than one output parameter in a procedure.
I would tell you to determine if, in fact, the results should be
returned as a resultset or as output parameters. If you think that
additional values may need to be returned in the future or if you think
there might be a time when more than one row needs to be returned, it's
better to use a resultset so you don't have to mess with the interface
of the procedure.
David Gugick
Imceda Software
www.imceda.com|||I am using the Microsoft Application Block for DataAccess and I cannot find
a
suitable procedure to call to bring back two output parameters. If this is
correct, I will need to extend the Application Block to include a procedure
to do what it is I need. Is this correct?
"David Gugick" wrote:

> Robert wrote:
> Output variables are generally faster to return than generating a 1 row
> result set. You can have more than one output parameter in a procedure.
> I would tell you to determine if, in fact, the results should be
> returned as a resultset or as output parameters. If you think that
> additional values may need to be returned in the future or if you think
> there might be a time when more than one row needs to be returned, it's
> better to use a resultset so you don't have to mess with the interface
> of the procedure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Robert wrote:
> I am using the Microsoft Application Block for DataAccess and I
> cannot find a suitable procedure to call to bring back two output
> parameters. If this is correct, I will need to extend the
> Application Block to include a procedure to do what it is I need. Is
> this correct?
>
I plead ignorance. I have never used the Microsoft Application Block for
DataAccess. I don't know how the object model looks. I assume it's a
high-level view of ADO.Net. Where do you define parameters for the
stored procedures. Make sure you're using the latest release, now called
the "Enterprise Library Patterns and Practices Library"
http://msdn.microsoft.com/library/d...li
b.asp
David Gugick
Imceda Software
www.imceda.com

Multiple tables from a dataset

Hi all,
Is it possible to use multiple tables being returned from the Stored
Procedure in a dataset on my report. it seems the dataset in the report
layout uses just the first data table.
thanx
regards,
KalpnaI remember reading somewhere in the book "Micrsoft Sql Server 2000 Reporting
Services" that reporting services do NOT support multiple datasets being
returned by stored procs.
-stan
"kalpna" <kalpna.prakash@.grapecity.com> wrote in message
news:u4Im5Q1gEHA.536@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to use multiple tables being returned from the Stored
> Procedure in a dataset on my report. it seems the dataset in the report
> layout uses just the first data table.
> thanx
> regards,
> Kalpna
>|||Hi
AnyBody can locate that MSDN link.
Thanks
Amit
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:#op8SZ6gEHA.1392@.TK2MSFTNGP11.phx.gbl...
> I remember reading somewhere in the book "Micrsoft Sql Server 2000
Reporting
> Services" that reporting services do NOT support multiple datasets being
> returned by stored procs.
> -stan
> "kalpna" <kalpna.prakash@.grapecity.com> wrote in message
> news:u4Im5Q1gEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > Is it possible to use multiple tables being returned from the Stored
> > Procedure in a dataset on my report. it seems the dataset in the report
> > layout uses just the first data table.
> >
> > thanx
> > regards,
> > Kalpna
> >
> >
>|||kalpna wrote:
> Hi all,
> Is it possible to use multiple tables being returned from the Stored
> Procedure in a dataset on my report. it seems the dataset in the
> report layout uses just the first data table.
Hello Kalpna,
What I experienced by now, is that you get only the data from the first
select statement. So I think you have to create a second datasource, as
I usually do, if i have two tables in my report.
Manfred

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 rows returned by for xml explicit

I have an xml query that runs across five tables. For some reasosn after I
get to the third table my xml returned is breaking into two rows in the
recordset that is returned. It just stops on the first record returned and
breaks mid element name to the second record. I have attached my query up
through table five. It breaks down on the last few closing elements and put
them on a second row. Not sure why this is happening and dealing with the
returned data is a pain. i have never seen for xml explicit returning the
xml as multiple rows before.
Rob
SELECT
1 AS Tag
,NULL AS Parent
,null as [catsubcatproduct!1!category]
,null as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,null as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
union
SELECT
2 AS Tag
,1 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,tcategory.catdesc as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,null as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory
--for xml explicit
union
SELECT
3 AS Tag
,2 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,tsubcategory.subcatname as [subcategory!3!subcatname!element]
,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,null as [product!4!productname!element]
,null as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
union
SELECT
4 AS Tag
,3 AS Parent
,null as [catsubcatproduct!1!category]
,tcategory.catname as [category!2!catname!element]
,null as [category!2!catdesc!element]
,null as [category!2!subcategory!element]
,tsubcategory.subcatname as [subcategory!3!subcatname!element]
,null as [subcategory!3!subcatdesc!element]
,null as [subcategory!3!product!element]
,tproduct.productname as [product!4!productname!element]
,tproduct.productdesc as [product!4!productdesc!element]
,null as [product!4!company!element]
,null as [company!5!companyname!element]
,null as [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner join
tproduct on tsubcatproduct.productname=tproduct.productname
order by[category!2!catname!element],[subcateg
ory!3!subcatname!element]
for xml explicitI assume that you use the query analyzer of SQL Server 2000 to see the
result?
If so, that is the wrong tool to get reliable XML results. XML is streamed
back in rowset chunks over TDS. In order to see the data as the stream it
is, you either use the command stream object in ADO/OLEDB (or the equivalent
ADO.Net API), or - if you use ODBC - you need to concatenate the result
yourself.
QA basically shows the rowsets explicit and thus adds the linebreaks every
2034 or so characters...
Note that this will be much better dealt with in the SQL Server 2005
management studio. Hyperlinked XML opens an XML editor. Pretty if I may
say so myself... :-)
Best regards
Michael
"roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>I have an xml query that runs across five tables. For some reasosn after I
> get to the third table my xml returned is breaking into two rows in the
> recordset that is returned. It just stops on the first record returned
> and
> breaks mid element name to the second record. I have attached my query up
> through table five. It breaks down on the last few closing elements and
> put
> them on a second row. Not sure why this is happening and dealing with the
> returned data is a pain. i have never seen for xml explicit returning the
> xml as multiple rows before.
> Rob
>
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,null as [catsubcatproduct!1!category]
> ,null as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> union
> SELECT
> 2 AS Tag
> ,1 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,tcategory.catdesc as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory
> --for xml explicit
> union
>
> SELECT
> 3 AS Tag
> ,2 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
>
> union
> SELECT
> 4 AS Tag
> ,3 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,tproduct.productname as [product!4!productname!element]
> ,tproduct.productdesc as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
> tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner
> join
> tproduct on tsubcatproduct.productname=tproduct.productname
>
> order by[category!2!catname!element],[subcateg
ory!3!subcatname!element]
> for xml explicit|||You are right. I was using the sql reader and not the xmlreader. The sql
reader returns records and the xmlreader returns one xml. If you use the
sqlreader you can concat the records and it will work but it is a waste to d
o
it that way. Thanks
Rob
"Michael Rys [MSFT]" wrote:

> I assume that you use the query analyzer of SQL Server 2000 to see the
> result?
> If so, that is the wrong tool to get reliable XML results. XML is streamed
> back in rowset chunks over TDS. In order to see the data as the stream it
> is, you either use the command stream object in ADO/OLEDB (or the equivale
nt
> ADO.Net API), or - if you use ODBC - you need to concatenate the result
> yourself.
> QA basically shows the rowsets explicit and thus adds the linebreaks every
> 2034 or so characters...
> Note that this will be much better dealt with in the SQL Server 2005
> management studio. Hyperlinked XML opens an XML editor. Pretty if I m
ay
> say so myself... :-)
> Best regards
> Michael
> "roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
> news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>
>

Multiple rows returned by for xml explicit

I have an xml query that runs across five tables. For some reasosn after I
get to the third table my xml returned is breaking into two rows in the
recordset that is returned. It just stops on the first record returned and
breaks mid element name to the second record. I have attached my query up
through table five. It breaks down on the last few closing elements and put
them on a second row. Not sure why this is happening and dealing with the
returned data is a pain. i have never seen for xml explicit returning the
xml as multiple rows before.
Rob
SELECT
1 AS Tag
,NULLAS Parent
,nullas [catsubcatproduct!1!category]
,nullas [category!2!catname!element]
,nullas [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,nullas [subcategory!3!subcatname!element]
,nullas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,nullas [product!4!productname!element]
,nullas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
union
SELECT
2 AS Tag
,1AS Parent
,nullas [catsubcatproduct!1!category]
,tcategory.catnameas [category!2!catname!element]
,tcategory.catdesc as [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,nullas [subcategory!3!subcatname!element]
,nullas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,nullas [product!4!productname!element]
,nullas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
from tcategory
--for xml explicit
union
SELECT
3 AS Tag
,2AS Parent
,nullas [catsubcatproduct!1!category]
,tcategory.catnameas [category!2!catname!element]
,nullas [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,tsubcategory.subcatnameas [subcategory!3!subcatname!element]
,tsubcategory.subcatdescas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,nullas [product!4!productname!element]
,nullas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
union
SELECT
4 AS Tag
,3AS Parent
,nullas [catsubcatproduct!1!category]
,tcategory.catnameas [category!2!catname!element]
,nullas [category!2!catdesc!element]
,nullas [category!2!subcategory!element]
,tsubcategory.subcatnameas [subcategory!3!subcatname!element]
,nullas [subcategory!3!subcatdesc!element]
,nullas [subcategory!3!product!element]
,tproduct.productnameas [product!4!productname!element]
,tproduct.productdescas [product!4!productdesc!element]
,nullas [product!4!company!element]
,nullas [company!5!companyname!element]
,nullas [company!5!companydesc!element]
from tcategory inner join tcatsubcat on tcategory.catname=tcatsubcat.catname
inner join
tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner join
tproduct on tsubcatproduct.productname=tproduct.productname
order by[category!2!catname!element],[subcategory!3!subcatname!element]
for xml explicit
I assume that you use the query analyzer of SQL Server 2000 to see the
result?
If so, that is the wrong tool to get reliable XML results. XML is streamed
back in rowset chunks over TDS. In order to see the data as the stream it
is, you either use the command stream object in ADO/OLEDB (or the equivalent
ADO.Net API), or - if you use ODBC - you need to concatenate the result
yourself.
QA basically shows the rowsets explicit and thus adds the linebreaks every
2034 or so characters...
Note that this will be much better dealt with in the SQL Server 2005
management studio. Hyperlinked XML opens an XML editor. Pretty cool if I may
say so myself... :-)
Best regards
Michael
"roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>I have an xml query that runs across five tables. For some reasosn after I
> get to the third table my xml returned is breaking into two rows in the
> recordset that is returned. It just stops on the first record returned
> and
> breaks mid element name to the second record. I have attached my query up
> through table five. It breaks down on the last few closing elements and
> put
> them on a second row. Not sure why this is happening and dealing with the
> returned data is a pain. i have never seen for xml explicit returning the
> xml as multiple rows before.
> Rob
>
> SELECT
> 1 AS Tag
> ,NULL AS Parent
> ,null as [catsubcatproduct!1!category]
> ,null as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> union
> SELECT
> 2 AS Tag
> ,1 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,tcategory.catdesc as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,null as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory
> --for xml explicit
> union
>
> SELECT
> 3 AS Tag
> ,2 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,tsubcategory.subcatdesc as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,null as [product!4!productname!element]
> ,null as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname
>
> union
> SELECT
> 4 AS Tag
> ,3 AS Parent
> ,null as [catsubcatproduct!1!category]
> ,tcategory.catname as [category!2!catname!element]
> ,null as [category!2!catdesc!element]
> ,null as [category!2!subcategory!element]
> ,tsubcategory.subcatname as [subcategory!3!subcatname!element]
> ,null as [subcategory!3!subcatdesc!element]
> ,null as [subcategory!3!product!element]
> ,tproduct.productname as [product!4!productname!element]
> ,tproduct.productdesc as [product!4!productdesc!element]
> ,null as [product!4!company!element]
> ,null as [company!5!companyname!element]
> ,null as [company!5!companydesc!element]
> from tcategory inner join tcatsubcat on
> tcategory.catname=tcatsubcat.catname
> inner join
> tsubcategory on tcatsubcat.subcatname=tsubcategory.subcatname inner join
> tsubcatproduct on tcatsubcat.subcatname=tsubcatproduct.subcatname inner
> join
> tproduct on tsubcatproduct.productname=tproduct.productname
>
> order by[category!2!catname!element],[subcategory!3!subcatname!element]
> for xml explicit
|||You are right. I was using the sql reader and not the xmlreader. The sql
reader returns records and the xmlreader returns one xml. If you use the
sqlreader you can concat the records and it will work but it is a waste to do
it that way. Thanks
Rob
"Michael Rys [MSFT]" wrote:

> I assume that you use the query analyzer of SQL Server 2000 to see the
> result?
> If so, that is the wrong tool to get reliable XML results. XML is streamed
> back in rowset chunks over TDS. In order to see the data as the stream it
> is, you either use the command stream object in ADO/OLEDB (or the equivalent
> ADO.Net API), or - if you use ODBC - you need to concatenate the result
> yourself.
> QA basically shows the rowsets explicit and thus adds the linebreaks every
> 2034 or so characters...
> Note that this will be much better dealt with in the SQL Server 2005
> management studio. Hyperlinked XML opens an XML editor. Pretty cool if I may
> say so myself... :-)
> Best regards
> Michael
> "roblenderman" <roblenderman@.discussions.microsoft.com> wrote in message
> news:73B244E1-6717-46A5-8C88-68A35AFAA66F@.microsoft.com...
>
>

Multiple Return Values

I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)

Right now, I use the statement "return @.@.Identity" for a single value, but there is another variable assigned in the procedure, @.NewCounselingRecordID that I need to pass back to the calling class method.

I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.

However, I feel this solution is kludgy. Is there a more correct way to accomplish this?

Thanks in advance for your comments.check out BOL for OUTPUT parameters..

hth|||Yes, that helps...I can't believe I brain farted on output parameters. (Duh :P)

Thanks