创建一个从任何表计数行的函数 [英] Create a function for counting rows from any table

问题描述

我有这个用户定义的函数：

  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 
  

问题是它只适用于表 [dbo] EMPLOYEE] ，我不想复制 - 粘贴此函数为我的数据库中的每个表。

我到目前为止的尝试：

  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 
  

该代码不起作用，因为它不允许函数内的这些语句。
它可以在存储过程中工作，但是只有当我 PRINT 变量而不是 RETURN

我需要它是一个功能，因为我需要在 IF 语句中调用它。 p>

有什么想法如何实现？谢谢。

解决方案

您可以使用下面的

  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）
和a.name=@TABLE_NAME 
 GROUP BY a.name 
 
 RETURN @ROW_COUNT 
 END 
 GO 
  

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

这篇关于创建一个从任何表计数行的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！