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) A socialist is someone who will give you the shirt off *someone else's* back.
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