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 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 Selects in a Stored Proc

I need to perform 2 selects in a stored procedure, but only return data from the second..

eg.

first select gets the proper table to search from a transaction summary table

select category from trans summary where transsummary.transid = @.transid

based on the category returned something like this

if category = 1
then tabletosearch = table1
end if
if category = 2
then tabletoseach = table 2
endif

select * from @.tabletosearch


Is this possible, or am I going about this the wrong way?...
As you probably can tell I am new at this.
Thanks folksTry something like...

select @.category = (select category from trans summary where transsummary.transid = @.transid)

if @.category = 1
then @.tabletosearch = table1
end if
if @.category = 2
then @.tabletoseach = table 2
endif

select * from @.tabletosearch|||DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||That worked great if I used SELECT * but as soon as I try to use only specific fields.....

Originally posted by achorozy

DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||I'm sorry if the example I showed was inline with your question, after all your question did have 'SELECT *'.

Since you don't tell us why it doesn't work when you list out the columns my only guess is that each table has different columns.

So why not just do this:

DECLARE @.category int

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SELECT col1, col2, col3 FROM table1

if (@.category = 2)
SELECT colA, colB, colC FROM table2
Sorry if I sound a bit off, but I've never asked a question and all of my 200+ posting has come from answering question, trying too anyways. So I get tired after awhile when someone says "It does work".
What doesn't work? What error did you get? Why doesn't it work?|||My apologies, but if it isnt obvious already, I am new to SQL and these newsgroups..again my apologies..

So far I am getting to about 7/8ths of the way thru want I need to do.
SO I will do, what I should have done the first time. give all the details

Here is what I want, I am able to get it to the first portion to work (select if...) so I won't go into more detail on that.

I need to be able to get data from 3 tables and only certain fields from each table

so

select IDCode.t1, IdDetail.t1, SalesRep.t1, SalesRep1Addr.t2, SalesRepEmail.t2, OfficeId.t2, OfficeAddr.t3, OfficeEmail.t3

S.. t1 contains a unique id from t2, and t2 contains a unique id from t3...

and obviously the the corresonding Sales Rep Detail & Office Detail...

Thanks so much for the help

Multiple Select statements + Stored Procedure

Hi all,

I have 2 select statements in my Stored Proc.

I want to display the results of each query in my DataGridView.

However, only the data of the last select query is returned.

Why is this?

Thanks.

At a time you can only bind one resultset. if you need both the resultset on your page, you need to have 2 datagrid view.

If you use dataset,

grid1.DataSource = dataset.Tables[0];

grid2.DataSource = dataset.Tables[1];

If you use datareader,

grid1.DataSource = datareader;

datareader.NextResultSet();

grid2.DataSource = datareader;

|||

Ideally I want all data displayed in one DataGridView.

Each SELECT Query returns the exact same Columns but differing data.

|||

As both queries return the same columns, could you use a union clause in your stored proc?

eg

Code Snippet

CREATE PROC proc

AS

SELECT col1, col2

FROM table1

UNION ALL
SELECT col1, col2

FROM table2

This would mean you'd have just one results set.

If its more complicated than that, you could put the results of each query into a temp table/variable and just select from that?

HTH!

|||

May be something like this...

Select 'Table 1' as Source, * From FirstTable

Union ALL

Select 'Table 2' as Source, * From SecondTable

Order By

Source, ....

|||If there any relation between the tables,you can group them in the gridview|||

Thanks!!

UNION ALL works.

multiple resultsets

I am just having many problems trying to call a procedure with multiple
parameters in multiple datasets. The proc works fine in query analyzer with
each parameter specified. I've got all the fields in each of the datasets,
and I've drug them all into the report layout. right now, my rpt is failing
with this:
The value expression for the textbox 'fieldname' refers to the fields
'fieldname'.
Report item expressions can only refer to fields within the current data set
scope or,
if inside and aggregate, the specified data set scope.
Can anybody tell me how to get around this? each of the datasets, when run
individually, work fine. (using the ! exclamation to run) but i receive
this error for one of the datasets when trying to preview the entire report.
-- Lynn
LynnI fixed that error. Somehow the fields of dataset1 got into the fields of
dataset5. but, now i get this:
An error has occurred during report processing.
Annot read the next data row for the data set DataSet3.
There is insufficient result space to convert a money value to varchar.
Again, each procedure call works fine in QA.
-- Lynn
"Lynn" wrote:
> I am just having many problems trying to call a procedure with multiple
> parameters in multiple datasets. The proc works fine in query analyzer with
> each parameter specified. I've got all the fields in each of the datasets,
> and I've drug them all into the report layout. right now, my rpt is failing
> with this:
> The value expression for the textbox 'fieldname' refers to the fields
> 'fieldname'.
> Report item expressions can only refer to fields within the current data set
> scope or,
> if inside and aggregate, the specified data set scope.
> Can anybody tell me how to get around this? each of the datasets, when run
> individually, work fine. (using the ! exclamation to run) but i receive
> this error for one of the datasets when trying to preview the entire report.
> -- Lynn
> Lynn|||I fixed that error, too. Now the report returns. But no values are returned
for Dataset1, dataset3 and dataset5. And, dataset2 and dataset4 only contain
duplicates.
for example, dataset2 is supposed to be like this:
Symbol #Trades Volume Total $
..
...
...
but i get only this:
CRNX 213
It returns only Symbol and #trades, and it displays only one, replicated
over and over. Same for dataset4. It should be:
EndPoint #Trades Volume Total $
...
....
.....
but i get only 5 of these:
6BM6 2
Any direction?
-- Lynn
"Lynn" wrote:
> I fixed that error. Somehow the fields of dataset1 got into the fields of
> dataset5. but, now i get this:
> An error has occurred during report processing.
> Annot read the next data row for the data set DataSet3.
> There is insufficient result space to convert a money value to varchar.
> Again, each procedure call works fine in QA.
> -- Lynn
>
> "Lynn" wrote:
> > I am just having many problems trying to call a procedure with multiple
> > parameters in multiple datasets. The proc works fine in query analyzer with
> > each parameter specified. I've got all the fields in each of the datasets,
> > and I've drug them all into the report layout. right now, my rpt is failing
> > with this:
> >
> > The value expression for the textbox 'fieldname' refers to the fields
> > 'fieldname'.
> > Report item expressions can only refer to fields within the current data set
> > scope or,
> > if inside and aggregate, the specified data set scope.
> >
> > Can anybody tell me how to get around this? each of the datasets, when run
> > individually, work fine. (using the ! exclamation to run) but i receive
> > this error for one of the datasets when trying to preview the entire report.
> >
> > -- Lynn
> > Lynn