Numbers of rows returned from mquery

I am using the following code to find if a use exits in the table:
declare @.UserName char(30)
set @.UserName = (select fullname from udf_GetUserName(@.UserId))
SELECT @.@.ROWCOUNT
but that always returns 1. How do I solve that?
ThanksYou are getting the rowcount value for the SET variable statement, which
obviously always equal to 1. If you'd like to know how many full name values
exist for a given identifier, you might consider using COUNT(*) like:
SELECT COUNT(*) FROM tbl_valued_udf ;
or avoid the assignment and do:
SELECT col FROM tbl_valued_udf ;
SELECT @.@.ROWCOUNT ;
Alternatively to check the existence, you could do:
IF EXISTS ( SELECT * FROM tbl_valued_udf )
Anith|||You solve it by asking the correct query. :)
What you are asking SQL to return is the number of rows affected by the
previous statement. Since the previous SELECT always returns a single row,
you get a rowcount of 1. What you really should do is select the fullname
from the underlying user table (or from an abstraction such as a view or
procedure) where the userID = @.userID (supplied parameter). If you get an
empty result set, then the user doesn't exist.
It looks like someone is trying to write an abstraction layer for
programmers so they won't have to learn any SQL. That leads to some really
bad code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:A494674C-147F-4009-8C40-99C163F6568D@.microsoft.com...
>I am using the following code to find if a use exits in the table:
> declare @.UserName char(30)
> set @.UserName = (select fullname from udf_GetUserName(@.UserId))
> SELECT @.@.ROWCOUNT
> but that always returns 1. How do I solve that?
> Thanks|||Why not just test IF IS NULL(@.UserName) instead?
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 12:47:01 -0700, Markgoldin
<Markgoldin@.discussions.microsoft.com> wrote:

>I am using the following code to find if a use exits in the table:
>declare @.UserName char(30)
>set @.UserName = (select fullname from udf_GetUserName(@.UserId))
>SELECT @.@.ROWCOUNT
>but that always returns 1. How do I solve that?
>Thanks