Cursor help please

[jazzpies]

Hello. I'm am trying to write a cursor in SQL Server 2000 that looks in a table with a list of tablenames (UpdateTables), looks up the number of rows in that table and then enters the number of rows in the second column of the lookup table against the correct tablename. I have written the following but am getting an error message saying that I need to declare the variable @tblName

GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_UpdateTablesUpdate]
as
DECLARE CrExtractUpdateCount CURSOR FOR SELECT Tablename FROM dbo.UpdateTables
DECLARE @tblName char(50)

OPEN CrExtractUpdateCount
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE UpdateTables
SET Number_Rows = (select count (*) from @tblName)
WHERE dbo.[UpdateTables].[Tablename] = @tblName
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName
END
CLOSE CrExtractUpdateCount
DEALLOCATE CrExtractUpdateCount

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


any help much appreciated.

[Senthil]

Hi,

Variable should be handled as follows.
go throu. the following lines for example..

DECLARE @tblName char(50)
set @tblName='Employee' --Table name
DECLARE @count char(500)
set @count='(select count (*) from ' + rtrim(isnull(@tblName,0)) + ')'
PRINT @count

Then Use the variable, @count to update the second column.
Hope U understand.

Regards,
Sharmila

[jazzpies]

Thanks for help but am now getting bad syntax errors around
set @count='(select count (*) from ' + rtrim(isnull(@tblName,0)) + ')'

[jazzpies]

The two SQL statements I have tried now are:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateTablesUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateTablesUpdate]
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_UpdateTablesUpdate]
as
DECLARE CrExtractUpdateCount CURSOR FOR SELECT Tablename FROM dbo.UpdateTables
DECLARE @tblName char(50)
DECLARE @NoRows char(50)

OPEN CrExtractUpdateCount
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE UpdateTables
SET Number_Rows = 'select count (*) from @tblName'
WHERE dbo.[UpdateTables].[Tablename] = @tblName
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName
END
CLOSE CrExtractUpdateCount
DEALLOCATE CrExtractUpdateCount

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


The above piece of code simply puts the text select count (*) from @tblName in each row in destination table.
There fore I tried:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateTablesUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateTablesUpdate]
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_UpdateTablesUpdate]
as
DECLARE CrExtractUpdateCount CURSOR FOR SELECT Tablename FROM dbo.UpdateTables
DECLARE @tblName char(50)
DECLARE @NoRows char(50)

OPEN CrExtractUpdateCount
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE UpdateTables
SET Number_Rows = (select count (*) from @tblName)
WHERE dbo.[UpdateTables].[Tablename] = @tblName
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName
END
CLOSE CrExtractUpdateCount
DEALLOCATE CrExtractUpdateCount

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Which is giving error
Server: Msg 137, Level 15, State 2, Procedure sp_UpdateTablesUpdate, Line 14
Must declare the variable '@tblName'.

I don't understand this as the variable is working perfectly in the next line.

I also tried putting the select statement into a variable called @NoRows which didn't work either (same two results as above)

Anymore help much appreciated.

[jazzpies]

Thanks for the help.
Managed to crack it by putting @NoRows into a temp table temprows then updating UpdateTables from this. As follows

if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[cp_UpdateTablesUpdate]') and OBJECTPROPERTY(id,

N'IsProcedure') = 1)
drop procedure [dbo].[cp_UpdateTablesUpdate]
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[cp_UpdateTablesUpdate]
as
DECLARE CrExtractUpdateCount CURSOR FOR SELECT Tablename FROM

dbo.UpdateTables
DECLARE @tblName char(50)
DECLARE @NoRows char(50)
DECLARE @NoRowsC char(50)

OPEN CrExtractUpdateCount
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
set @NoRows = 'select count (*) from ' + @tblName
insert into temprows exec (@NoRows)
UPDATE UpdateTables
SET Number_Rows = temprows.execreturn
from temprows
WHERE dbo.[UpdateTables].[Tablename] = @tblName
FETCH NEXT FROM CrExtractUpdateCount
INTO @tblName
END
CLOSE CrExtractUpdateCount
DEALLOCATE CrExtractUpdateCount

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

exec cp_UpdateTablesUpdate