how to return only last select from stored procedure

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
TomislawTomislaw,
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|||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
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:E655A96C-643E-40F1-B045-D6F3DC713737@.microsoft.com...
> 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
>|||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
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:OwLMrsjSFHA.248@.TK2MSFTNGP15.phx.gbl...
> 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
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:E655A96C-643E-40F1-B045-D6F3DC713737@.microsoft.com...
>|||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 Outpu
t
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 wil
l
be populated with the values in the Last Row of the resultset, which, withou
t
an Order By, may not be absolutely determinisitic,
"TomislaW" wrote:

> 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
>
>
>|||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.
David Portas
SQL Server MVP
--|||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 ar
e.
You should find that David's example works veryw ell.
Rob|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:ED19C5E3-A554-4976-B0E0-4C9631A95C78@.microsoft.com...
> 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

How to retreive set of rows from stored procedure

hi,

i am new to SQL. i have created a stored procedure which gets a input parameter "Category" and it selects datas which falls under this category. when i run this procedure it returns only the last row. it doesnt retreive the entire set of rows. which method should i follow to solve my problem.. i want all the rows which comes under the category to be returned ....

MY PROCEDURE

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10),

@.ProductID Char(10) OUTPUT,

@.Name Char(50) OUTPUT,

@.UnitPrice Numeric(9) OUTPUT,

@.Stock Numeric(9) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

SELECT @.ProductID=ProductID,

@.Name=Name,

@.UnitPrice=UnitPrice,

@.Stock =Stock

From

ProductDetails

Where

Category=@.Category

END

As you're using output parameters, you can only have one value per parameter and this will be set to values in the last row returned by your query.

To return a recordset of multiple values, try:

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10)

)

AS

BEGIN

SET NOCOUNT ON;

SELECT ProductID,

Name,

UnitPrice,

Stock

From

ProductDetails

Where

Category=@.Category

END

Hope this helps!