EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure