Member Avatar for Nfurman

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?

  • 4 Contributors
  • 7 Replies
  • 279 Views
  • 5 Months Discussion Span
  • Latest Post Latest Post by skgopy

Dani AI

Generated

A short, practical clarification that ties the replies together and fixes the root cause.

The error message shown by happens because the plain SELECT inside the function was producing a resultset instead of supplying a value for the function to return. Scalar user-defined functions cannot send result sets to the client — they must either assign a value to a local variable and return it, or directly return an expression. 's approach (assigning then returning) is correct; 's note about calling the function with the schema is also important (functions live in a schema).

A minimal alternative that returns the count directly (no extra local variable) looks like this:

CREATE FUNCTION dbo.getRows(@id INT)
RETURNS INT
AS
BEGIN
  RETURN (SELECT COALESCE(COUNT(*), 0) FROM dbo.MainData WHERE id = @id);
END;

For better performance or composability, prefer an inline table-valued function or just run the COUNT(*) in the calling query. Example inline TVF:

CREATE FUNCTION dbo.getRowsTVF(@id INT)
RETURNS TABLE
AS
RETURN
(
  SELECT RowCount = COUNT(*) FROM dbo.MainData WHERE id = @id
);

Troubleshooting tips: if SQL complains the function name is not recognized, verify the function exists in the correct database and schema (for example, check OBJECT_ID('dbo.getRows')), refresh or reopen the session/IntelliSense — a full SQL Server restart is not normally required. Avoid NOLOCK unless dirty reads are acceptable. Finally, for 's later question: to get the current database name use the built-in DB_NAME() (for example, SELECT DB_NAME() AS DatabaseName;) — AS_REP() is not a built-in function.

Recommended Answers

All 7 Replies

Member Avatar for sknake

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
Member Avatar for Nfurman

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

Member Avatar for sknake

What do you mean "doesnt work" ?

Member Avatar for MeSampath

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.

Member Avatar for Nfurman

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

Member Avatar for skgopy

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.

Member Avatar for skgopy

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.