@@RowCount

I want to run one of two SELECT statements, one with a Join the other
without, depending on wheteher the Join fails because of an unrelated record
.
The second statement should always return one row as the @.RequestID value
being passed comes from a Tree selection. But no row is returned. Where am I
going wrong? TIA
I am using:
CREATE PROCEDURE tdGetRequestor
@.RequestID BigInt
AS
SELECT tblRequests.RequestID, tblRequests.RequestedBy,
tblRequests.RequestDate, tblRequests.RequestorSectionID,
tblSections.SectionName AS RequestorSection
FROM tblRequests
INNER JOIN tblSections ON tblRequests.RequestorSectionID =
tblSections.SectionID
WHERE tblRequests.RequestID = @.RequestID
IF @.@.ROWCOUNT=0
BEGIN
SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
FROM tblRequests
WHERE tblRequests.RequestID = @.RequestID
END
GOHave you tried the second query in outside of the SP by providing @.RequestID
manually?
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
> record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
> I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||I'm not sure there is an use for RowCount here. Is there a reason why you
can't use an outer join? This should return the same results, with NULL for
tblSections.SectionName if a related record does not exist.
CREATE PROCEDURE tdGetRequestor
@.RequestID BigInt
AS
SELECT
tblRequests.RequestID
, tblRequests.RequestedBy
, tblRequests.RequestDate
, tblRequests.RequestorSectionID
, tblSections.SectionName AS RequestorSection
FROM tblRequests
LEFT OUTER JOIN tblSections
ON tblRequests.RequestorSectionID = tblSections.SectionID
WHERE tblRequests.RequestID = @.RequestID
GO
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
> I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||While I agree with Jim that you proably could use an outer join for this
(almost assuredly) have you tried to run this in Query Analyzer? See what
happens there. If you get two result sets, then it is probably something in
how you are trying to deal with 2 resultsets (which would be a question for
someone in the newsgroup that corresponds to your language (programming
language, of course :))
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
> record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
> I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||Save @.@.rowcount in an integer variable immediately after the Transact-SQL
statement completes. The value of the variable can be used later.
declare @.rowcount int
set @.rowcount = @.@.rowcount
HTH
Rajesh Peddireddy
"td" wrote:

> I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated reco
rd.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||Good thinking! I'm glad I thought of that (=: (or should have thought of tha
t)
"Jim Underwood" wrote:

> I'm not sure there is an use for RowCount here. Is there a reason why you
> can't use an outer join? This should return the same results, with NULL f
or
> tblSections.SectionName if a related record does not exist.
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT
> tblRequests.RequestID
> , tblRequests.RequestedBy
> , tblRequests.RequestDate
> , tblRequests.RequestorSectionID
> , tblSections.SectionName AS RequestorSection
> FROM tblRequests
> LEFT OUTER JOIN tblSections
> ON tblRequests.RequestorSectionID = tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> GO
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
> record.
> I
>
>|||Yes. It works fine. As I mentioned, the second statement will always return
a
row.
"Leila" wrote:

> Have you tried the second query in outside of the SP by providing @.Request
ID
> manually?
>
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>
>|||Hello Louis, Thanks for your input.
I did take Jim's advise & it works fine.
On your suggestion I have run the query in Query Analyser and it does run
correctly. In VB .Net the Reader is reporting that .HasRows is false.
Thanks to all for your suggestions
What gets returned by SQL Server in a situation such as this? It does seem
that it is reporting the zero rows back from the first query and, perhaps,
returning the result of the second as well?
In Query Analyser two distinct results ARE displayed. I will write some more
code and check what is happening in VB .Net.
td
"Louis Davidson" wrote:

> While I agree with Jim that you proably could use an outer join for this
> (almost assuredly) have you tried to run this in Query Analyzer? See what
> happens there. If you get two result sets, then it is probably something
in
> how you are trying to deal with 2 resultsets (which would be a question fo
r
> someone in the newsgroup that corresponds to your language (programming
> language, of course :))
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>
>