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 return a value from SP

Hi all,

How to return a value from a store procedure?

I use a VBA to call a store procedure, but I would like to be able to return the result back to a variable.

Here is an VBA example:

Dim GetDestinationID As Long

Dim conConnection As ADODB.Connection
Dim StrSQL As String

Set conConnection = CurrentProject.Connection

StrSQL = "usp_GetDestinationID " & 2 & ", " & _
GetDestinationID

conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

I would like to return GetDestinationID.

Here is the SP:

CREATE PROCEDURE [dbo].[usp_GetDestinationID]

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT

)

AS

BEGIN

set @.intDestinationID=(SELECT lv.DestinationID
FROM [Land Voyages] AS lv INNER JOIN [Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID)

END
GO

What is wrong? Can I return a value to a Visual Basic Application from a Store Procedure?

Regardshttp://dbforums.com/t916861.html|||HI all

I found a solution to my question about VBA calling a Stored Procedure and returning a value

VBA:
'-------------------
Private Function GetDestinationID(OrderID As Long) As Long
On Error GoTo GetDestinationID_Err

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = CurrentProject.Connection
.CommandText = "usp_GetDestinationID"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.intOrderID", adInteger, adParamInput, , OrderID)
.Parameters.Append .CreateParameter("@.intDestinationID", adInteger, adParamOutput)
.Execute
GetDestinationID = .Parameters("@.intDestinationID").Value
End With

WrapUp:


Exit_GetDestinationID:
Set cmd = Nothing
Exit Function

GetDestinationID_Err:
Call LogMsgError(Err.Description, Err.Number, ModuleName$, "GetDestinationID")
Resume Exit_GetDestinationID
End Function

'----------------
T-SQL:

CREATE PROCEDURE dbo.usp_GetDestinationID

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

SELECT @.intDestinationID=lv.DestinationID
FROM dbo.[Land Voyages] AS lv INNER JOIN dbo.[Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID

END
GO
'-----------

Thanks to Igor for suggestions.

Dani