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_IDENTIFI ER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_UpdateTables Update]
as
DECLARE CrExtractUpdate Count CURSOR FOR SELECT Tablename FROM dbo.UpdateTable s
DECLARE @tblName char(50)

OPEN CrExtractUpdate Count
FETCH NEXT FROM CrExtractUpdate Count
INTO @tblName

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

GO

SET QUOTED_IDENTIFI ER 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='Emplo yee' --Table name
DECLARE @count char(500)
set @count='(select count (*) from ' + rtrim(isnull(@t blName,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(@t blName,0)) + ')'

[jazzpies]

The two SQL statements I have tried now are:

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


SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_UpdateTables Update]
as
DECLARE CrExtractUpdate Count CURSOR FOR SELECT Tablename FROM dbo.UpdateTable s
DECLARE @tblName char(50)
DECLARE @NoRows char(50)

OPEN CrExtractUpdate Count
FETCH NEXT FROM CrExtractUpdate Count
INTO @tblName

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

GO

SET QUOTED_IDENTIFI ER 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_UpdateTables Update]') and OBJECTPROPERTY( id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateTables Update]
GO


SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp_UpdateTables Update]
as
DECLARE CrExtractUpdate Count CURSOR FOR SELECT Tablename FROM dbo.UpdateTable s
DECLARE @tblName char(50)
DECLARE @NoRows char(50)

OPEN CrExtractUpdate Count
FETCH NEXT FROM CrExtractUpdate Count
INTO @tblName

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

GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

Which is giving error
Server: Msg 137, Level 15, State 2, Procedure sp_UpdateTables Update, 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_UpdateTables Update]') and OBJECTPROPERTY( id,

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


SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[cp_UpdateTables Update]
as
DECLARE CrExtractUpdate Count CURSOR FOR SELECT Tablename FROM

dbo.UpdateTable s
DECLARE @tblName char(50)
DECLARE @NoRows char(50)
DECLARE @NoRowsC char(50)

OPEN CrExtractUpdate Count
FETCH NEXT FROM CrExtractUpdate Count
INTO @tblName

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

GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

exec cp_UpdateTables Update