What's wrong w/ my cursor? 1

[icemel]

Below is the cursor. Can't figure out why it doesn't like:
SELECT count(*) FROM @name

Thanks

------------

ALTER PROCEDURE myProc
AS
BEGIN

DECLARE @name varchar(255)

DECLARE c1 CURSOR FOR
SELECT sysobjects.name as TableName FROM sysobjects
WHERE type = 'U'
AND OBJECTPROPERTY(sysobjects.id , 'TableHasIndex' ) = 0

SET NOCOUNT ON;

OPEN c1

FETCH NEXT FROM c1
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT count(*) FROM @name -- ERROR

FETCH NEXT FROM c1
INTO @name

END

CLOSE c1
DEALLOCATE c1

END

----

ERROR:
Msg 1087, Level 15, State 2, Procedure proc_MissingIndexes, Line 25
Must declare the table variable "@name".

 

[gmmastros]

Is this what you are trying to do with that cursor?

Create Table #Temp(TableName VarChar(255), RecordCount Int)

exec sp_msforeachtable 'Insert Into #Temp(TableName, RecordCount) Select ''?'', count(*) from ?'

Select * From #Temp

Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Nothing's wrong with cursor, just you can't use variable as a table to select, you could use dynamic SQL:

ALTER  PROCEDURE myProc
AS
BEGIN

    DECLARE @name varchar(255)
    DECLARE @lcSQL varchar(2000)

    DECLARE c1 CURSOR FOR
    SELECT sysobjects.name as TableName FROM sysobjects
    WHERE type = 'U'
    AND OBJECTPROPERTY(sysobjects.id , 'TableHasIndex' ) = 0

    SET NOCOUNT ON;

    OPEN c1

    FETCH NEXT FROM c1
    INTO @name

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @lcSQL = 'SELECT count(*) FROM '+ @name
            EXEC(@lcSQL)

            FETCH NEXT FROM c1 INTO @name
        END

    CLOSE c1
    DEALLOCATE c1

END


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[icemel]

Hi,

Yes, I tried the dynamic sql approach, too.

Using your exact code, the error returned is:

Msg 137, Level 15, State 1, Procedure myProc, Line 25
Must declare the scalar variable "@lcSQL".
Msg 137, Level 15, State 2, Procedure myProc, Line 26
Must declare the scalar variable "@lcSQL".

??

 

[bborissov]

It works for me, did you have:

DECLARE @lcSQL varchar(2000)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[icemel]

yes, works now thanks