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 if recordcount = 0

How would I return a select statement if the records returned is 0
i.e
Select * from orders where employeeID = 0
if @.RowCount = 0
--Return Shipname as 'None'
Select 'None' = shipname ect,
Thanks
Stephen K. Miyasato
MDsyncHi Stephen,
I would do something like...
SELECT * FROM Orders where EmployeeID = 0
IF @.@.ROWCOUNT = 0
SELECT 'None' As ShipName
or
IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
SELECT * FROM Orders WHERE EmployeeID = 0
ELSE
SELECT 'None' As ShipName
Both examples would return a single row with a single column called
[ShipName] with the value 'None' in the event that no rows exist with an
employeeID of 0.
Rich.
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
> How would I return a select statement if the records returned is 0
> i.e
> Select * from orders where employeeID = 0
> if @.RowCount = 0
> --Return Shipname as 'None'
> Select 'None' = shipname ect,
>
> Thanks
> Stephen K. Miyasato
> MDsync
>|||Try this:
Select * from orders where employeeID = 0
if @.@.RowCount = 0
begin
--Return Shipname as 'None'
Select 'None' as 'shipname'
end|||Thanks but I could not get this to work.
Here is the actual query:
The query as it now is return two sets. The first set has 0 rows, the next
set is the one I need to produce.
if I use Exist(Select ..), I get and error since I have NTEXT values and
order by is not allowed.
Any other suggustions?
Stephen K. Miyasato
/****** Object: Table [dbo].[orderTest] Script Date: 5/9/2006 12:42:23
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orderTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [orderTest] (
[PatNo] [int] NULL ,
[Name] [varchar] (24) NULL ,
[Dosage] [varchar] (25) NULL ,
[Sig] [varchar] (30) NULL ,
[PatSig] [varchar] (50) NULL ,
[RxNo] [int] NULL ,
[Breakfast] [varchar] (1) NULL ,
[Lunch] [varchar] (1) NULL ,
[Dinner] [varchar] (1) NULL ,
[QHS] [varchar] (1) NULL ,
[Comments] [varchar] (60) NULL ,
[QuantityL] [varchar] (20) NULL ,
[Quantity] [float] NULL ,
[Generic] [varchar] (60) NULL ,
[BrandName] [varchar] (60) NULL ,
[MDrx] [int] NULL ,
[Category_id] [int] NULL ,
[Daily] [smallint] NULL ,
[Memo] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
Declare @.PatNo int
Set @.PatNo = 7160
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic, BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo) AND (StopDate IS NULL)
ORDER BY Daily
if @.@.RowCount = 0
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig, Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as Category_id,
Null as Daily,
Null as Memo
"Richard O'Brien" <groups@.rjoconsulting.co.uk> wrote in message
news:ecp6gH6cGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Hi Stephen,
> I would do something like...
> SELECT * FROM Orders where EmployeeID = 0
> IF @.@.ROWCOUNT = 0
> SELECT 'None' As ShipName
> or
> IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
> SELECT * FROM Orders WHERE EmployeeID = 0
> ELSE
> SELECT 'None' As ShipName
> Both examples would return a single row with a single column called
> [ShipName] with the value 'None' in the event that no rows exist with an
> employeeID of 0.
> Rich.
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
>|||Stephen,
Select * from orders where employeeID = 0
union all
select
-- whatever values you want
1,2,3,4,...
where not exists(Select 1 from orders where employeeID = 0)|||StopDate isn't a column in the table definition you gave.
Try this.
Declare @.PatNo int
Set @.PatNo = 7160
if exists (select * from ordertest where (PatNo = @.PatNo)) -- AND
(StopDate IS NULL))
begin
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic,
BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo)-- AND (StopDate IS NULL)
ORDER BY Daily
end
else begin
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig,
Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as
Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as
Category_id,
Null as Daily,
Null as Memo
end

How to retrieve @@Rowcount variable

I'm trying to output the number of rows that were effected by my stored proc.

Here is the stored proc:

ALTER Proc Update_IndividualMoves_GTPhone_NCOAPhone_Differ
AS
Update Results
SET Results.home_phone = Results.NEWPhone,
Results.Address1 = Results.NCOAADDRESS1,
Results.CITY = Results.NCOACITY,
Results.ST = Results.NCOAST,
Results.ZIP_OUT = Results.NCOAZIP5,
Results.ZIP4_OUT = Results.NCOAZ4
Where AddressServiceStatus = 'I' AND home_phone IS NOT NULL AND NEWPhone IS NOT NULL AND home_phone <> NEWPhone
Return @.@.Rowcount

Here is the code from the DAL class that I'm calling the stored procedure from (I'm using the SQL Helper Class.)

 Public Shared Function GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ()
 Dim Rowcount As Integer
 Dim GlobalConnString As String = AppSettings("ConnectionString")
 ''Put proc in that gets this data out for household moves that have both
 ''GTPro and NCOA update phone numbers however they differ. Does not apply
 ''to DRC donors / < 12 month donors. Update to latest and greatest phone number
 ''from NCOA listing.
 Try
 Return ExecuteDataset(GlobalConnString, CommandType.StoredProcedure, "Update_HouseholdMoves_GTPhone_NCOAPhone_Differ", New SqlParameter("@.@.Rowcount", Rowcount))
 Catch ex As Exception
 Throw New ApplicationException("An error occured when calling this stored proc out Update_HouseholdMoves_GTPhone_NCOAPhone_Differ")
 End Try
 End Function

I want to post how many rows were effected in a label that is located on my aspx page through referencing the function above:

What I'm doing is activating the function through an asp:button control and then I want to display the @.@.Rowcount result in the label next to it.

Here is what I have now:

<code
Private Sub cmdHouseholdMove2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdHouseholdMove2.Click
'Dim rowcount As Integer
GetAddressIncorrect_HH_GTPhone_NCOAPhone_Differ(New SqlParameter("@.@.rowcount", lblHouseholdMoves2.Text))

End Sub

If anyone knows how to do this please let me know:

Thanks in advance everyone.

Regards,
RByou can use an OUTPUT parameter to return the rowcount...check books on line for sample code.
some sample code for retreiving the output parameter from asp.net..

dim result as integer
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)
result = convert.toint16(mycommand.Parameters("@.result").Value))

hth