May 23, 2018 at 2:34 pm
I have 88 tables that are updated with new records. I need to count the number of loaded records and place them into a simple audit table (TableName, NumberOfRecords, Date). I want to use a cursor but have a problem (this is a fragment of procedure without the Insert part):
Create proc [dbo].[ServSP_AuditAllTables_test]
as
declare curTables cursor for
SELECT TableName FROM AllTablesList
declare @TableName varchar(100)
declare @RecordCount int
open curTables
fetch next from curTables into @TableName
while @@FETCH_STATUS=0
begin
print @TableName
set @RecordCount=(Select count(*) from @TableName )
print cast( @RecordCount as varchar(20))
fetch next from curTables into @TableName
end
close curTables
deallocate curTables
======================================
I get this error message:
Msg 1087, Level 16, State 1, Procedure ServSP_AuditAllTables_test, Line 12
Must declare the table variable "@TableName".
I declared it did not I?
What's the problem?
May 23, 2018 at 3:24 pm
valeryk2000 - Wednesday, May 23, 2018 2:34 PMI have 88 tables that are updated with new records. I need to count the number of loaded records and place them into a simple audit table (TableName, NumberOfRecords, Date). I want to use a cursor but have a problem (this is a fragment of procedure without the Insert part):Create proc [dbo].[ServSP_AuditAllTables_test]
as
declare curTables cursor for
SELECT TableName FROM AllTablesList
declare @TableName varchar(100)
declare @RecordCount int
open curTables
fetch next from curTables into @TableName
while @@FETCH_STATUS=0
begin
print @TableName
set @RecordCount=(Select count(*) from @TableName )
print cast( @RecordCount as varchar(20))
fetch next from curTables into @TableName
end
close curTables
deallocate curTables
======================================
I get this error message:
Msg 1087, Level 16, State 1, Procedure ServSP_AuditAllTables_test, Line 12
Must declare the table variable "@TableName".
I declared it did not I?
What's the problem?
Because you can't use a variable in that manner in a FROM clause. To accomplish what you are doing you will need to use dynamic SQL.
That means you need to rethink your process a bit. The first thing you should research is sp_executesql paying attention to how to pass out a value using it. The clue here is OUTPUT.
May 23, 2018 at 3:51 pm
CREATE PROCEDURE [dbo].[ServSP_AuditAllTables_test]
AS
SET NOCOUNT ON;
INSERT INTO dbo.audit_table ( TableName, NumberOfRecords, Date )
SELECT ATL.TableName, CA1.row_count, GETDATE() AS date
FROM AllTablesList ATL
CROSS APPLY (
SELECT SUM(rows) AS row_count
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID(ATL.TableName) AND
index_id IN (0, 1)
) AS CA1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 23, 2018 at 4:34 pm
ScottPletcher - Wednesday, May 23, 2018 3:51 PM
CREATE PROCEDURE [dbo].[ServSP_AuditAllTables_test]
ASSET NOCOUNT ON;
INSERT INTO dbo.audit_table ( TableName, NumberOfRecords, Date )
SELECT ATL.TableName, CA1.row_count, GETDATE() AS date
FROM AllTablesList ATL
CROSS APPLY (
SELECT SUM(rows) AS row_count
FROM sys.partitions p
WHERE p.object_id = OBJECT_ID(ATL.TableName) AND
index_id IN (0, 1)
) AS CA1
Definitely a better way to go but then wait for the ubiquitous reply that you may not get the correct result. And no, I am not giving that, just expecting it.
May 24, 2018 at 8:02 am
Thank you, Scott and Lynn. The code works perfectly well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy