Row Count

I am writing a Insert stored procedure that has logic which is going to
do different things based on the results of a query.
The query is going to do a count. If the count is not equal to 0, then
the procedure returns setting a @.msg paramenter and -1 in the return
code.
I can get this working just fine, but when the count > 0, a rowset is
returned and I am not wanting the rowset returned. Is there someway to
structure the procedure such that a rowset is not returned Here is
what I have...
alter proc Proc_InsertCompetativeEvents
@.Name char(50),
@.City char(50),
@.State char(50),
@.StartDt smalldatetime,
@.EndDt smalldatetime,
@.msg nvarchar(220) out
as
declare @.cnt int
select count(CompetitionName)
from dbo.CompetativeEvents
where CompetitionName = @.name
group by CompetitionName
if @.@.ROWCOUNT > 0
begin
set @.msg = 'Duplicate Event Names are not allowed'
return -1
end
insert into dbo.CompetativeEvents
Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
set @.cnt = @.@.ROWCOUNT
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred inserting a record into
CompetativeEvents using the Proc_InsertCompetativeEvents procedure -
Error code: ' + cast(@.@.ERROR as char(4))
return -1
end
if @.cnt = 1
begin
set @.msg=''
return 0
end
set @.msg = 'Beat me what happen'
return -1
Thanks in advance for your assistance!!!!!!!!Use IF EXISTS:
IF EXISTS
(SELECT *
FROM CompetitiveEvents
WHERE competitionname = @.name)
SET @.msg = 'Duplicate Event Names are not allowed'
...
--
David Portas
--
Please reply only to the newsgroup
--|||Jim,
I'm not sure what you want, but could you explain what you are
intending to do with
select count(CompetitionName) ...
if @.@.ROWCOUNT > 0
If I'm not mistaken, @.@.ROWCOUNT will always be greater than zero after a
select count() query.
Your narrative says that you want to compare the count with zero, but
your code does not compare the count with zero. It compares the number
of count(CompetitionName) values returned (the rowcount of the query)
with zero, and that value looks like it will always be 1, since you
restrict to CompetitionName = @.name before grouping.
SK
Jim Heavey wrote:
>I am writing a Insert stored procedure that has logic which is going to
>do different things based on the results of a query.
>The query is going to do a count. If the count is not equal to 0, then
>the procedure returns setting a @.msg paramenter and -1 in the return
>code.
>I can get this working just fine, but when the count > 0, a rowset is
>returned and I am not wanting the rowset returned. Is there someway to
>structure the procedure such that a rowset is not returned Here is
>what I have...
>alter proc Proc_InsertCompetativeEvents
> @.Name char(50),
> @.City char(50),
> @.State char(50),
> @.StartDt smalldatetime,
> @.EndDt smalldatetime,
> @.msg nvarchar(220) out
>as
>declare @.cnt int
>select count(CompetitionName)
> from dbo.CompetativeEvents
> where CompetitionName = @.name
> group by CompetitionName
>if @.@.ROWCOUNT > 0
> begin
> set @.msg = 'Duplicate Event Names are not allowed'
> return -1
> end
>insert into dbo.CompetativeEvents
> Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
>set @.cnt = @.@.ROWCOUNT
>if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred inserting a record into
>CompetativeEvents using the Proc_InsertCompetativeEvents procedure -
>Error code: ' + cast(@.@.ERROR as char(4))
> return -1
> end
>if @.cnt = 1
> begin
> set @.msg=''
> return 0
> end
>set @.msg = 'Beat me what happen'
>return -1
>
>Thanks in advance for your assistance!!!!!!!!
>|||Jim Heavey (JimHeavey@.nospam.com) writes:
> I can get this working just fine, but when the count > 0, a rowset is
> returned and I am not wanting the rowset returned. Is there someway to
> structure the procedure such that a rowset is not returned Here is
> what I have...
As Steve pointed out, this does not make sense:
> declare @.cnt int
> select count(CompetitionName)
> from dbo.CompetativeEvents
> where CompetitionName = @.name
> group by CompetitionName
> if @.@.ROWCOUNT > 0
What you most likely want is:
SELECT @.cnt = COUNT(*)
FROM CompetativeEvents
WHERE CompetitionName = @.name
IF @.cnt > 0
(Note that I also left out GROUP BY.)
Of course, even better is David's suggestion to use IF EXISTS. Not much
of an issue here, but if there could be 1000 rows, and you are only
interested whether in 0 or not zero, there is no reason to count them
all.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||> If I'm not mistaken, @.@.ROWCOUNT will always be greater than zero after a
> select count() query.
Because the query includes GROUP BY, @.@.ROWCOUNT will return 0 if the @.name
doesn't exist but it will never be more than 1 because of the combination of
GROUP BY and the WHERE clause.
Since the point seems to be to test for the presence or absence of a value I
suggested using EXISTS. Of course, another option is just to do the insert
and trap the error at the client rather than pass a message from the SP.
--
David Portas
--
Please reply only to the newsgroup
--|||David Portas wrote:
>>If I'm not mistaken, @.@.ROWCOUNT will always be greater than zero after a
>>select count() query.
>>
>Because the query includes GROUP BY, @.@.ROWCOUNT will return 0 if the @.name
>doesn't exist but it will never be more than 1 because of the combination of
>GROUP BY and the WHERE clause.
>
Oops - good catch. This is one place where GROUP BY can reduce the
number of rows returned...
SK
>Since the point seems to be to test for the presence or absence of a value I
>suggested using EXISTS. Of course, another option is just to do the insert
>and trap the error at the client rather than pass a message from the SP.
>
>|||Hi
As an aside, the following code will be problematic if an error occurs:
insert into dbo.CompetativeEvents
Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
set @.cnt = @.@.ROWCOUNT
if @.@.ERROR > 0
The set resets the @.@.ERROR variable back to 0 so if an error occurs during
the insert, you don't get it.
To resolve this, store the error in a variable and get both values back at
the same time:
SELECT @.cnt = @.@.ROWCOUNT, @.LocalError = @.@.Error
if @.LocalError > 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9465C570A8BFEJimHeaveyhotmailcom@.207.46.248.16...
> I am writing a Insert stored procedure that has logic which is going to
> do different things based on the results of a query.
> The query is going to do a count. If the count is not equal to 0, then
> the procedure returns setting a @.msg paramenter and -1 in the return
> code.
> I can get this working just fine, but when the count > 0, a rowset is
> returned and I am not wanting the rowset returned. Is there someway to
> structure the procedure such that a rowset is not returned Here is
> what I have...
> alter proc Proc_InsertCompetativeEvents
> @.Name char(50),
> @.City char(50),
> @.State char(50),
> @.StartDt smalldatetime,
> @.EndDt smalldatetime,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> select count(CompetitionName)
> from dbo.CompetativeEvents
> where CompetitionName = @.name
> group by CompetitionName
> if @.@.ROWCOUNT > 0
> begin
> set @.msg = 'Duplicate Event Names are not allowed'
> return -1
> end
> insert into dbo.CompetativeEvents
> Values (newid(), @.Name,@.City,@.State,@.StartDt, @.EndDt)
> set @.cnt = @.@.ROWCOUNT
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred inserting a record into
> CompetativeEvents using the Proc_InsertCompetativeEvents procedure -
> Error code: ' + cast(@.@.ERROR as char(4))
> return -1
> end
> if @.cnt = 1
> begin
> set @.msg=''
> return 0
> end
> set @.msg = 'Beat me what happen'
> return -1
>
> Thanks in advance for your assistance!!!!!!!!