Count all rows from necessary tables using cursor

Question

I have few tables in my database and I want to count total rows of all those tables based on AppoitmentID for which I use a scalar cursor and a table variable to store some data of those tables based on AppoitmentID. After the end of cursor I count rows of table variable in which I had inserted data using dynamic query in cursor.

But it gives me the following error

Must declare the table variable "@ProcCount".

Is there any other way to get the count of all rows from necessary tables. Below is my Code :

 Create FUNCTION [dbo].[ufn_GetProcedureCount] 
(   

)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @MenuID bigINT, @TableName VARCHAR(150);    
    DECLARE @Result int
    DECLARE @ProcCount TABLE (AppID INT, WoundId bigINT,TableName varchar(150));

    DECLARE @sql nvarchar(2000)
    DECLARE @Count int


     DECLARE Cur_PendSign Cursor For 
        select Distinct MenuID,TableName from AppointmentTypeRequiredDocumnet  A inner join Menu M on  M.ID =A.MenuID where m.MenuGroupID = 8

    OPEN Cur_PendSign 

    FETCH Cur_PendSign INTO @MenuID, @TableName
    WHILE @@FETCH_STATUS=0
    BEGIN

SET @sql='DECLARE @ProcCount TABLE (AppID INT, WoundId bigINT,TableName varchar(150))'
SET @sql=@sql+'INSERT INTO @ProcCount (AppID,WoundId)
            SELECT TOP 1 V.AppointmentID, 1
            FROM ['+@TableName+'] V WITH(NOLOCK)'
set @sql=@sql+ 'select count(*) from @ProcCount;'


--set @sql=@sql+ 'DECLARE @Count int'
 EXECUTE sp_executesql @sql 

FETCH Cur_PendSign INTO @MenuID, @TableName
    END
CLOSE  Cur_PendSign
DEALLOCATE  Cur_PendSign


  --set  @Result = select count(*) from @ProcCount

    RETURN @Result

END

Are you sure you need cursor and cannot achieve what you need just with joins? Cause cursor reduces performance — Pream, Mar 09 '17 at 09:42
@Pream, I need a cursor because I use it return value in my store procedure. — Hitesh, Mar 09 '17 at 09:46
You are picking only `TOP 1` from dynamic code insert. Then How many number of tables present in cursor that only the count. Is n't? — Shakeer Mirza, Mar 09 '17 at 09:47
@Shakeer, there more than 15 tables in cursor. I have use top 1 to check whether is working or not. — Hitesh, Mar 09 '17 at 09:49
IS there any other way instead of using variable or temp table? — Hitesh, Mar 09 '17 at 09:54
Look at this http://stackoverflow.com/questions/11360317/table-variable-and-exec — etsa, Mar 09 '17 at 09:56

score 0 · Answer 1 · answered Mar 09 '17 at 09:41

0

There are two issues with the script.

First: Missing where clause

select Distinct MenuID,TableName from Appointment A.AppointmentID = 8

Second: you need to create persistent table rather than variable table due to limitation of scope as you cant declare variable table outside of EXEC and use it.

answered Mar 09 '17 at 09:41

Bilal Ayub

142
4

This is demo script to ask a question. How to create persistent table and how to use it in this query. – Hitesh Mar 09 '17 at 09:44
Use temp table (##tablename) or normal table. – Bilal Ayub Mar 09 '17 at 09:45
temp table is not allowed in cursor so I use variable table – Hitesh Mar 09 '17 at 09:47
There is no such thing as temp table is not allowed in cursor. – Bilal Ayub Mar 09 '17 at 09:54
Sorry My mistake, We can not use temp table in user defined scalar function and I am using cursor inside function. – Hitesh Mar 09 '17 at 09:56

score 0 · Answer 2 · answered Mar 09 '17 at 09:59

This query should work for you. You need to use sp_executesql to execute dynamic queries.

CREATE FUNCTION [dbo].[ufn_GetProcedureCount]
(

)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result INT

    DECLARE @ProcCount TABLE (
        AppID INT,
        WoundId BIGINT,
        TableName VARCHAR(150)
    )
    DECLARE @MenuID BIGINT,
            @TableName VARCHAR(150)

    --Get all table which I need to count rows
    DECLARE Cur_PendSign CURSOR FOR
    SELECT DISTINCT MenuID, TableName FROM Appointment WHERE AppointmentID = 8
    OPEN Cur_PendSign

    FETCH Cur_PendSign INTO @MenuID, @TableName
    WHILE @@fetch_status = 0
    BEGIN

        -- Insert require data into @ProcCount using dynamic query
        DECLARE @query VARCHAR(255) = 'INSERT INTO @ProcCount (AppID,WoundId,TableName) 
                                       SELECT TOP 1 AppointmentID, WoundId, TableName
                                       FROM [' + @TableName + ']  WITH(NOLOCK) '

        EXECUTE sys.sp_executesql @query

        FETCH Cur_PendSign INTO @MenuID, @TableName
    END
    CLOSE Cur_PendSign
    DEALLOCATE Cur_PendSign

    --Get Count of all rows from tables
    SELECT @Result = COUNT(*) FROM @ProcCount

    RETURN @Result

END

@Hitesh, it works for me on SQL Server 2008. Can you please add the table definition, insert scripts, test data and the actual error message as you receive from SQL Server with Severity and State for clear understanding of your issue. — Venu, Mar 09 '17 at 10:28
@Vena, I just Updated my code but every cursor loop it created a new variable table so i can not get count after cursor — Hitesh, Mar 09 '17 at 10:41
@Hitesh, Can you please also add sample data and the expected output? Please refer to https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Venu, Mar 09 '17 at 10:46

score 0 · Answer 3 · answered Mar 09 '17 at 10:00

Without having a cursor, Loops you can do it with Dynamic coding..

Schema:

(It may be differ with your actual schema)

CREATE TABLE #Appointment (MENUID INT IDENTITY,AppointmentID INT, TableName VARCHAR(20))
INSERT INTO #Appointment
SELECT 1,'TABLE1'
UNION ALL
SELECT 2, 'TABLE2'
UNION ALL
SELECT 8,'TABLE3'
UNION ALL
SELECT 8,'TABLE4'
UNION ALL
SELECT 8,'TABLE5'

Now do like below

DECLARE @QRY VARCHAR(MAX)='';

SELECT @QRY = @QRY+ 'SELECT COUNT(1) AS COUNT_TABLES FROM '+ TableName + ' (NOLOCK)  
UNION ALL
' FROM (
SELECT DISTINCT  TableName FROM #Appointment A WHERE  A.AppointmentID = 8
)A

SELECT @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-11)

SELECT @QRY = '
SELECT SUM(COUNT_TABLES) FROM (

' + @QRY+'
)A'

--PRINT @QRY

EXEC (@QRY)

If you want to check what @QRY contains

/*
SELECT SUM(COUNT_TABLES) FROM (

SELECT COUNT(1) AS COUNT_TABLES FROM TABLE3 (NOLOCK)  
UNION ALL
SELECT COUNT(1) AS COUNT_TABLES FROM TABLE4 (NOLOCK)  
UNION ALL
SELECT COUNT(1) AS COUNT_TABLES FROM TABLE5 (NOLOCK)  

)A
*/

Count all rows from necessary tables using cursor

3 Answers3