make a count a local variable 1

[revilord]

I trying to get a count from a number of tables and use that count as a variable in MS SQL. I have to get the table names from sysobjects so I am using an EXEC. here's what I want but this won't work. I have let out all the loops and other stuff.

DECLARE @query varchar(100)
DECLARE @counter varchar(5)
DECLARE @table_name varchar(20)
SET @table_name = 'sysusers' --

select @query = 'select count(*) from ' + @table_name
EXEC (@query) = @counter -- this fails
If (CAST (@counter) AS int) = 0
BEGIN
Print '0 reocrds in ' + @table name
END

 

[SQLDenis]

http://sqlservercode.blogspot.com/2006/03/use-spexecutesql-or-exec-to-get-count.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

so basically this way, example is using pubs modify for your need

USE pubs
GO

--sp_executesql
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'Authors'
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[revilord]

Thanks Denis. Worked perfectly.