Dynamic Function

how about modifying function to get dynamic string and execute outside?

Create FUNCTION dbo.[f_Rec_cnt] 
        (@InTableName NVARCHAR(100),
         @InDtColName NVARCHAR(50),
         @InDate NVARCHAR(50)
        )
RETURNS varchar(max)
AS
BEGIN

DECLARE @vTableName NVARCHAR(150)
DECLARE @vDtColName NVARCHAR(50)
DECLARE @vInDate NVARCHAR(50)
DECLARE @vSql NVARCHAR(500)
SELECT @vTableName=@InTableName,@vDtColName=@InDtColName,@vInDate=@InDate
      SELECT @vInDate=''''+CAST(@InDate AS NVARCHAR)+''''
      SELECT @vSql='SELECT COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
      RETURN @vSql 
END

and

Declare @SQLStr varchar(max) 
DECLARE @tmptable table (rows int)
set @SQLStr=dbo.f_Rec_cnt('HumanResources.Department', 'ModifiedDate' , '01-Jun-2012')
print @SQLStr
insert into @tmptable
Exec (@SQLStr)
select * from @tmptable

---

Balmukund Lakhani

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker

--------------------------------------------------------------------------------

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------------------------------------------------------------------

My Blog | Team Blog | @Twitter | Facebook

Author: SQL Server 2012 AlwaysOn - Paperback, Kindle