SQL Function

[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?

[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

[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

[sknake]

What do you mean "doesnt work" ?

[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.

[Nfurman]

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

[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.

[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.