Please someone can help me. I need a function which just returns the number of rows from known table. I tried it like:

CREATE FUNCTION getRows
(
@id int
)
RETURNS int
AS
BEGIN
	DECLARE @rownums int
	SELECT Count(*) FROM dbo.MainData
	where id=@id
	RETURN @rownums
END

But it throws me

Msg 444, Level 16, State 2, Procedure getRows, Line 9
Select statements included within a function cannot return data to a client.

what's wrong with my select?

Recommended Answers

Please post SQL questions in the [URL="http://www.daniweb.com/forums/forum127.html"]MS SQL Forum[/URL]

To answer your question:
[code=sql]
CREATE FUNCTION getRows
(
@id int
)
RETURNS int
AS
BEGIN
DECLARE @rownums int
Set @rownums = IsNull((SELECT Count(*) FROM …

Jump to Post

All 7 Replies

Re: SQL Function 80 80

Please post SQL questions in the MS SQL Forum

To answer your question:

CREATE FUNCTION getRows
(
@id int
)
RETURNS int
AS
BEGIN
	DECLARE @rownums int
	Set @rownums = IsNull((SELECT Count(*) FROM dbo.MainData (NOLOCK) where id=@id), 0)
	RETURN @rownums
END
Re: SQL Function 80 80

Please post SQL questions in the MS SQL Forum

To answer your question:

CREATE FUNCTION getRows
(
@id int
)
RETURNS int
AS
BEGIN
	DECLARE @rownums int
	Set @rownums = IsNull((SELECT Count(*) FROM dbo.MainData (NOLOCK) where id=@id), 0)
	RETURN @rownums
END

thank you but this code doesn't work

Re: SQL Function 80 80

What do you mean "doesnt work" ?

Re: SQL Function 80 80

Hi,

The function given by Sknake is perfect. It is working properly.

The thing is while calling the function you may get an error like

Msg 195, Level 15, State 10, Line 1
'getRows' is not a recognized built-in function name.

because, we are calling the function without dbo

If you are getting this error means call the function as,

select dbo.getRows(1)

Please let us know if you get any other errors.

Thank you.

Re: SQL Function 80 80

Ok. thank you today it works. Looks like sometimes it's useful to restart SqlServer.:icon_wink:
Thank you for helping me.

Re: SQL Function 80 80

im trying to select a database as the current one.

SELECT AS_REP() DATABASENAME
i get this error:Msg 195, Level 15, State 10, Line 1.'AS_REP' is not a recognized built-in function name.
when i try to call it with a dbo:SELECT dbo.AS_REP() DATABASENAME
i get this error:Msg 4121, Level 16, State 1, Line 1.Cannot find either column "dbo" or the user-defined function or aggregate "dbo.AS_REP", or the name is ambiguous.

Re: SQL Function 80 80

im trying to select a database as the current one.

SELECT AS_REP() DATABASENAME
i get this error:Msg 195, Level 15, State 10, Line 1.'AS_REP' is not a recognized built-in function name.
when i try to call it with a dbo:SELECT dbo.AS_REP() AS DATABASENAME
i get this error:Msg 4121, Level 16, State 1, Line 1.Cannot find either column "dbo" or the user-defined function or aggregate "dbo.AS_REP", or the name is ambiguous.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.