I have this user-defined function:
CREATE FUNCTION [dbo].[COUNT_ROWS_TABLE]()
RETURNS TINYINT
AS
BEGIN
DECLARE @ROW_COUNT TINYINT
SELECT @ROW_COUNT = COUNT(*) FROM EMPLOYEE
RETURN @ROW_COUNT
END
GO
The problem is it only works for the table [dbo].[EMPLOYEE] and I don't want to Copy-Paste this function for every table on my database.
My attempt so far:
CREATE FUNCTION [dbo].[COUNT_ROWS_TABLE](@TABLE_NAME VARCHAR(50))
RETURNS TINYINT
AS
BEGIN
DECLARE @SQL_COMMAND NVARCHAR(100)
DECLARE @PARAM NVARCHAR(50)
DECLARE @ROW_COUNT TINYINT
SET @SQL_COMMAND = N'SELECT @RESULT = COUNT(*) FROM ' + @TABLE_NAME
SET @PARAM = N'@RESULT TINYINT OUTPUT'
EXEC SP_EXECUTESQL @SQL_COMMAND, @PARAM, @RESULT = @ROW_COUNT OUTPUT
RETURN @ROW_COUNT
END
GO
That code does not work because it doesn't allow those statements inside a function. It works inside a stored procedure, though, but only if I PRINT the variable rather than RETURN it.
I need it to be a function, since I need to call it on an IF statement.
Any thoughts on how to achieve this? Thank you.
You can use like below
ALTER FUNCTION [dbo].[COUNT_ROWS_TABLE](@TABLE_NAME VARCHAR(50))
RETURNS TINYINT
AS
BEGIN
DECLARE @ROW_COUNT TINYINT
SELECT @ROW_COUNT = SUM(b.rows)
FROM SYS.TABLES a INNER JOIN sys.partitions b ON b.OBJECT_ID = a.OBJECT_ID
WHERE a.is_ms_shipped = 0 AND b.index_id IN (1,0)
and a.name=@TABLE_NAME
GROUP BY a.name
RETURN @ROW_COUNT
END
GO
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments