marvid

marvid

  • NA
  • 1
  • 0

EXEC in SQL functions

Aug 31 2006 8:01 AM
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


Answers (1)