how to return only last select from stored procedure

[TomislaW]

I have 3 selects in my stored procedure

I am using first and second select just for calculating number of rows
(@@ROWCOUNT), and last one is the final select that I need. But I get all
three selects



Tomislaw

[Rob Farley]

Tomislaw,

If the first two are only for calculating the number of rows, then use

select @numrows = count(*)
from ....

instead of doing the select query and referencing @@rowcount.

If you do a select query in a stored procedure, it will be returned as a
result set.

Rob

[TomislaW]

it is not so simple
my select is:

SELECT Date1, Date2, Neto, Provision
FROM TERMIN
WHERE (ID = @ID)
AND (Date1>= @Date1 AND DatumDo <= @Date2)
GROUP BY Date1, Date2, Neto, Provision
HAVING (MAX(BedsNo) >= @BedsNo)

i get 2 rows
with count i get number 3

Post by Rob Farley
Tomislaw,
If the first two are only for calculating the number of rows, then use
from ....
If you do a select query in a stored procedure, it will be returned as a
result set.
Rob

[Uri Dimant]

Hi
Try this
create proc myproc
as
set nocount on
declare @row int
declare @t table (col int)
insert into @t select orderid from orders
select @row=@@rowcount
select * from orders
go
exec myproc

Post by TomislaW
it is not so simple
SELECT Date1, Date2, Neto, Provision
FROM TERMIN
GROUP BY Date1, Date2, Neto, Provision
i get 2 rows
with count i get number 3

Post by Rob Farley
Tomislaw,
If the first two are only for calculating the number of rows, then use
from ....
If you do a select query in a stored procedure, it will be returned as a
result set.
Rob

[David Portas]

There should be no reason why you can't get the same result with COUNT as
with @@ROWCOUNT:

SELECT COUNT(*)
FROM
(SELECT NULL
FROM termin
WHERE id = @id
AND date1 >= @date1
AND datumdo <= @date2
GROUP BY date1, date2, neto, provision
HAVING MAX(bedsno) >= @bedsno)T(x)

If you still have a problem then please post DDL as well as your query so
that we can test it out.

[Rob Farley]

The issue with count(*) in your original context, Tomislaw, is that the
"having" filter is applied after the aggregates are calculated. Therefore,
you could get a different solution. But using count(*) is still the right
way, you just need to nest it as in David's example.

He puts 'select null', because there's no point in returning a whole bunch
of rows in a subquery where you're only interested in how many rows there are.

You should find that David's example works veryw ell.

Rob

[TomislaW]

Post by Rob Farley
The issue with count(*) in your original context, Tomislaw, is that the
"having" filter is applied after the aggregates are calculated. Therefore,
you could get a different solution. But using count(*) is still the right
way, you just need to nest it as in David's example.
He puts 'select null', because there's no point in returning a whole bunch
of rows in a subquery where you're only interested in how many rows there are.
You should find that David's example works veryw ell.
Rob

Thanks to you and David
TomislaW

[CBretana]

What ar you doing with the data returned from the first two selects? Why
are you even executing them if you don;t want the results passed back to the
client?

Either you want to use the data as a parameter value in the last Select, or
to decide whether or not to even run the last Select. Either way, The Output
of a Select statement in a Stored Proc is either returned to the client or
not returned, depending on whether the output values are being assigned to
T-SQL Variables or not. If even one output value is NOT being assigned t oa
T-SQL Variable, then the Output will be returned t othe CLient.

Select @VarA = <expresssionA>, @Varb = <ExpressionB>, <ExpressionC>, etc.
From ...

If ALL The output values are being assigned to T-SQL Variables, then the
result set will NOT be sent back to the client.
Select @VarA = <ExpresssionA>,
@Varb = <ExpressionB>,
@VarC = <ExpressionC>
From ...

In the latter case, you have to be careful that the Select only generates
one row, because if it generates more than one, then the T-SQL Variables will
be populated with the values in the Last Row of the resultset, which, without
an Order By, may not be absolutely determinisitic,

Post by TomislaW
I have 3 selects in my stored procedure
I am using first and second select just for calculating number of rows
three selects
Tomislaw