Dynamic Function to return number of rec in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Function to return number of rec in table

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-08-05 : 00:53:01

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

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-08-05 : 01:11:04

Came to know that function cannot have dynamic sql as above, then converted to SP but still getting the result as NULL in the output parameter?


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
      SELECT  @InRowCnt = @vRowCnt
END

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources