none
Dynamic Function

    Question

  • I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue,
    and returns number of records in that table for that particular date(in parameter date), 
    I have written below function but it is not returning the desired result, can anyone help me.

    CREATE FUNCTION dbo.[f_Rec_cnt] 
    (@InTableName NVARCHAR(100),
    @InDtColName NVARCHAR(50),
    @InDate NVARCHAR(50)
            )
    RETURNS INT
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 @vRowCnt = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          EXECUTE SP_EXECUTESQL @vSql
          RETURN (CAST(@vRowCnt AS INT))
    END

    • Edited by Neilcse Tuesday, August 05, 2014 4:50 AM
    Tuesday, August 05, 2014 4:45 AM

Answers

  • 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

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:01 AM
    Tuesday, August 05, 2014 5:16 AM
    Moderator
  • I changed it to SP as suggested, but it is returning null, where as table has data pertaining to the date passed

    ALTER PROCEDURE [p_Rec_cnt] 
    			   (
    					@InTableName NVARCHAR(100),
    					@InDtColName NVARCHAR(50),
    					@InDate NVARCHAR(50),
    					@InRowCnt  INT OUTPUT
    			   )
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 '+@vRowCnt+' = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT
          SELECT  @InRowCnt = @vRowCnt
    END


    Neil



    You're not using it as suggested

    please refer the link


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:00 AM
    Tuesday, August 05, 2014 5:17 AM
  • I changed it to SP as suggested, but it is returning null, where as table has data pertaining to the date passed

    ALTER PROCEDURE [p_Rec_cnt] 
    			   (
    					@InTableName NVARCHAR(100),
    					@InDtColName NVARCHAR(50),
    					@InDate NVARCHAR(50),
    					@InRowCnt  INT OUTPUT
    			   )
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 '+@vRowCnt+' = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT
          SELECT  @InRowCnt = @vRowCnt
    END


    Neil

    ALTER PROCEDURE [p_Rec_cnt] 
    (
    @InTableName NVARCHAR(100),
    @InDtColName NVARCHAR(50),
    @InDate NVARCHAR(50)
    )
    AS
    BEGIN
    DECLARE @vSql NVARCHAR(500)
    DECLARE @InCnt INT
    SELECT @vSql='SELECT @Cnt = COUNT(*) FROM '+@InTableName+' WHERE '+@InDtColName+'= @InDate '
    PRINT (@vSql)
    EXECUTE SP_EXECUTESQL @vSql, N'@InDate NVARCHAR(50) , @Cnt NVARCHAR(50) OUTPUT', @InDate  , @cnt = @InCnt OUTPUT
    SELECT @InCnt
    END
    
    test this 


    Thanks and regards, Rishabh K

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:00 AM
    Tuesday, August 05, 2014 5:26 AM
  • You have to specify OUTPUT .

    Try:

    DECLARE @vRowCnt INT
    EXEC [P_Rec_cnt] 'test','v_date','2014-08-04 23:51:12.923',@vRowCnt OUTPUT
    SELECT @vRowCnt
    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:00 AM
    Tuesday, August 05, 2014 6:30 AM
  • This KB article explains how to use OUTPUT parameters with sp_executesql

    http://support.microsoft.com/kb/262499


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:01 AM
    Tuesday, August 05, 2014 5:05 AM

All replies

  • Dynamic SQL is not supported in UDFs

    You need to make it a stored procedure with OUTPUT parameter to return count value

    http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, August 05, 2014 4:53 AM
  • Also the way you have written dynamic sql is in itself incorrect for eg: the variable @vrowCnt is not available in dynamic sql context.



    Thanks and regards, Rishabh K

    Tuesday, August 05, 2014 4:57 AM
  • Replace

    EXECUTE SP_EXECUTESQL @vSql

    with

    EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT

    Set @vRowCount to Output.

    Hope this helps.
    ~ J.

    Tuesday, August 05, 2014 4:58 AM
  • This KB article explains how to use OUTPUT parameters with sp_executesql

    http://support.microsoft.com/kb/262499


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:01 AM
    Tuesday, August 05, 2014 5:05 AM
  • I changed it to SP as suggested, but it is returning null, where as table has data pertaining to the date passed

    ALTER PROCEDURE [p_Rec_cnt] 
    			   (
    					@InTableName NVARCHAR(100),
    					@InDtColName NVARCHAR(50),
    					@InDate NVARCHAR(50),
    					@InRowCnt  INT OUTPUT
    			   )
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 '+@vRowCnt+' = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT
          SELECT  @InRowCnt = @vRowCnt
    END


    Neil



    • Edited by Neilcse Tuesday, August 05, 2014 5:13 AM
    Tuesday, August 05, 2014 5:07 AM
  • 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

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:01 AM
    Tuesday, August 05, 2014 5:16 AM
    Moderator
  • I changed it to SP as suggested, but it is returning null, where as table has data pertaining to the date passed

    ALTER PROCEDURE [p_Rec_cnt] 
    			   (
    					@InTableName NVARCHAR(100),
    					@InDtColName NVARCHAR(50),
    					@InDate NVARCHAR(50),
    					@InRowCnt  INT OUTPUT
    			   )
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 '+@vRowCnt+' = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT
          SELECT  @InRowCnt = @vRowCnt
    END


    Neil



    You're not using it as suggested

    please refer the link


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:00 AM
    Tuesday, August 05, 2014 5:17 AM
  • I changed it to SP as suggested, but it is returning null, where as table has data pertaining to the date passed

    ALTER PROCEDURE [p_Rec_cnt] 
    			   (
    					@InTableName NVARCHAR(100),
    					@InDtColName NVARCHAR(50),
    					@InDate NVARCHAR(50),
    					@InRowCnt  INT OUTPUT
    			   )
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 '+@vRowCnt+' = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT
          SELECT  @InRowCnt = @vRowCnt
    END


    Neil

    ALTER PROCEDURE [p_Rec_cnt] 
    (
    @InTableName NVARCHAR(100),
    @InDtColName NVARCHAR(50),
    @InDate NVARCHAR(50)
    )
    AS
    BEGIN
    DECLARE @vSql NVARCHAR(500)
    DECLARE @InCnt INT
    SELECT @vSql='SELECT @Cnt = COUNT(*) FROM '+@InTableName+' WHERE '+@InDtColName+'= @InDate '
    PRINT (@vSql)
    EXECUTE SP_EXECUTESQL @vSql, N'@InDate NVARCHAR(50) , @Cnt NVARCHAR(50) OUTPUT', @InDate  , @cnt = @InCnt OUTPUT
    SELECT @InCnt
    END
    
    test this 


    Thanks and regards, Rishabh K

    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:00 AM
    Tuesday, August 05, 2014 5:26 AM
  • Replace your @vSql with this:

    SELECT @vSql='SELECT @vRowCnt = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'>='+@vInDate

    @vRowCnt has to be inside the dynamic SQL context.

    ~ J.

    Tuesday, August 05, 2014 5:32 AM
  • Hi Jonathan,

    This is returning NULL, when I print the @sql it is printing correct sql query but result is NULL, from SP when I run the generated SQL independently it is returning value

    ALTER PROCEDURE [P_Rec_cnt] 
    			   (
    					@InTableName NVARCHAR(100),
    					@InDtColName NVARCHAR(50),
    					@InDate NVARCHAR(50),
    					@InRowCnt  INT OUTPUT
    			   )
    AS
    BEGIN
    DECLARE @vRowCnt NVARCHAR(50)
    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 @vRowCnt = COUNT(*) FROM '+@vTableName+' WHERE '+@vDtColName+'='+@vInDate
          --SELECT (@vSql)
          EXECUTE SP_EXECUTESQL @vSql, N'@vRowCnt NVARCHAR(50) OUTPUT', @vRowCnt OUTPUT
          --SELECT  @InRowCnt = @vRowCnt
    END


    Neil

    Tuesday, August 05, 2014 5:47 AM
  • Neil,

    Uncomment the last line:

    SELECT  @InRowCnt = @vRowCnt

    @InRowCnt is the name of the parameter to output from the SP and the last line assign value of @vRowCnt to it.

    Tuesday, August 05, 2014 5:50 AM
  • Hi Jonathan,

    I did this change, but still getting the NULL as output, when I run the generated Sql statement, I am getting the desired result, but not through the sp


    Neil

    DECLARE @vRowCnt INT
    EXEC [P_Rec_cnt] 'test','v_date','2014-08-04 23:51:12.923',@vRowCnt
    SELECT @vRowCnt

    output is null

    SELECT  COUNT(*) FROM test WHERE v_date='2014-08-04 23:51:12.923'

    output 2 (record)


    • Edited by Neilcse Tuesday, August 05, 2014 6:23 AM
    Tuesday, August 05, 2014 6:00 AM
  • You have to specify OUTPUT .

    Try:

    DECLARE @vRowCnt INT
    EXEC [P_Rec_cnt] 'test','v_date','2014-08-04 23:51:12.923',@vRowCnt OUTPUT
    SELECT @vRowCnt
    • Marked as answer by Neilcse Tuesday, August 05, 2014 7:00 AM
    Tuesday, August 05, 2014 6:30 AM
  • Yes now it is working, thanks.  I need to test this with different data set.  As I am comparing date without type casting can this cause a problem in returning correct data?

    As of now this is working for some of the tables I've tested.  Any data comparison is tedious task :)

    Thanks Jonathan, Visakh and all others

    Regards,


    Neil

    Tuesday, August 05, 2014 6:37 AM