EXEC in SQL functions

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 dont 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 a way round this ?

Thanks.

Vidhya

There are two problems with your code. One as you mentioned is to do with restrictions with what you can do inside a function. Exec'ing sql isn't allowed. So you have to use a sproc.

2ndly, when you exec sql, it is quite tricky to get information back out again. You can't just declare a variable before you exec and then use it in the exec.
The code below should give you the results your looking for (but it seems to me that this can be obtained by other means - I'll post again if I remember how.)

CREATE PROC dbo.spRowCount(@tblName nvarchar(1000), @intRowCount int OUTPUT)

DECLARE @RowCnt int

DECLARE @ExecString nvarchar(1000)

SELECT @ExecString='select @i=count(*) from ' + @tblName

SELECT @ExecString

exec sp_executesql @ExecString,N'@i int output', @RowCnt output

SELECT @RowCnt

SET @intRowCount=@RowCnt

DECLARE @intResult int

exec dbo.spRowCount N'tblNonMFTShareclassDetails',@intResult output

SELECT @intResult