How to get return value from stored procedure with dynamic SQL query-CodePudding

The problem is to display all the table name from database having more than 10 rows and columns.

I have a cursor which is successfully returning table names with more than 10 columns, but when I try to count all the rows of particular table by passing table name as variable, I get an error like can not pass object as variable thus trying to get return value (all row count) using stored procedure with dynamic SQL.

I want to get return result as all row count from stored procedure thus I can pass it to another variable into cursor.

DECLARE @TABLENAME VARCHAR(50) 
DECLARE @COUNTROW INT
DECLARE @COLUMNCOUNT INT
DECLARE @ROWCOUNT INT

DECLARE TABLECURSOR CURSOR SCROLL FOR 
    SELECT NAME FROM SYS.TABLES 

OPEN TABLECURSOR 
FETCH NEXT FROM TABLECURSOR INTO @TABLENAME 

WHILE @@FETCH_STATUS = 0
BEGIN
    --EXEC @COUNTROW = USP_XX_EXECUTESQL @TABLENAME     --[ CALL SP AND RETURN RESULT TO @COUNTROW ] 
        
    SELECT @COLUMNCOUNT = COUNT(*) 
    FROM INFORMATION_SCHEMA.Columns 
    WHERE TABLE_NAME = @TABLENAME 
        
    IF (@COLUMNCOUNT > 10)
    BEGIN
        PRINT @TABLENAME
    END

    FETCH NEXT FROM TABLECURSOR INTO @TABLENAME 
END

CLOSE TABLECURSOR
DEALLOCATE TABLECURSOR


----STORED PROCEDURE TO COUNT ROWS 

CREATE PROCEDURE USP_XX_EXECUTESQL(@TABLE VARCHAR(MAX))
AS
BEGIN
    EXEC('SELECT COUNT(*) FROM '   @TABLE)     -- How to return value from here 
END

CodePudding user response：

Here is answer

   DECLARE @sql    nvarchar(MAX)
    DECLARE @TABLE NVARCHAR(30)
    DECLARE  @params nvarchar(4000)
         
    SET @TABLE = N'AGENT'
    SELECT @sql = N'SELECT @cnt = COUNT(*) 
                    FROM  '   @TABLE 
    
       SELECT @params = N'   @cnt int OUTPUT'
                     
                   
    
    DECLARE @cnt  int`enter code here`
    EXEC sp_executesql  @sql,  @cnt OUTPUT
    SELECT @cnt  AS TOTALROW

CodePudding user response：

You don't really need a cursor for this anyway, you could generate the SQL and execute it in one shot dynamically.

But in any case, it's far better to just query the system views for rowcounts

SELECT
  SchemaName = SCHEMA_NAME(t.schema_id),
  TableName = t.name,
  TotalRowCount = (
      SELECT SUM(p.rows)
      FROM sys.partitions p
      WHERE t.object_id = p.object_id
        AND p.index_id IN ( 0, 1 )  -- heap or clustered
  ),
  TotalColumns = (
      SELECT COUNT(*)
      FROM sys.columns c
      WHERE t.object_id = c.object_id
  )
FROM sys.tables t;