EXEC in SQL functions

Question

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

David · Answer

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 ) AS 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 GO DECLARE @intResult int exec dbo . spRowCount N 'tblNonMFTShareclassDetails'  @intResult output SELECT @intResult