trying to get a rowcount to return from a UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trying to get a rowcount to return from a UDF

Author

Topic

sjlsam
Starting Member

9 Posts

Posted - 2007-08-22 : 11:29:02

[code]
CREATE FUNCTION dbo.BlogCountComments(@postPK char(35))

RETURNS VARCHAR(1000) AS

BEGIN
DECLARE @a_variable varchar(1000)

SELECT @a_variable = 0

FROM
dbo.BlogComments comments

WHERE comments.commentPost = @postPK

RETURN @@rowcount
END
[/code]

this UDF returns the number of comments that exist for a post
the UDF is working like i want, but it is inefficient because i am using @a_variable

how can i get rid of the @a_variable from the SELECT?

i have tried to fix the situation like this:
[code]
CREATE FUNCTION dbo.BlogCountComments(@postPK char(35))

RETURNS VARCHAR(1000) AS

BEGIN

SELECT *

FROM
dbo.BlogComments comments

WHERE comments.commentPost = @postPK

RETURN @@rowcount
END
[/code]

This returns:
Error 444: Select statements included within a function cannot return data to a client.

any idea of what i can do?
thanks for your help

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-22 : 11:43:06

Try it this way:

CREATE FUNCTION dbo.BlogCountComments(@postPK char(35))
RETURNS VARCHAR(1000) AS
BEGIN
RETURN (SELECT COUNT(*) FROM dbo.BlogComments comments WHERE comments.commentPost = @postPK)
END

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 11:44:47

[code]CREATE FUNCTION dbo.BlogCountComments
(
@postPK char(35)
)
RETURNS INT AS
BEGIN
RETURN (
SELECT COUNT(*)
FROM dbo.BlogComments
WHERE commentPost = @postPK
)
END[/code]

E 12°55'05.25"
N 56°04'39.16"

sjlsam
Starting Member

9 Posts

Posted - 2007-08-22 : 11:50:52

ahh that makes sense
awesome, thanks for the fast response!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources