Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-24 : 19:13:18
|
Jim writes "I'm trying to loop through all the tables in a database and get some simple statistics. I'd like to execute a dynamic query where the table name changes. I know this can be performed using EXEC as in:
DECLARE @s VARCHAR(128), @TableName VARCHAR(40)
SET @TableName = 'Person'
SELECT @s = 'SELECT * FROM ' + @TableName
EXEC (@s)
But, I'd like to have some parameters set and returned, as in the following snippet (which of course doesn't work):
CREATE PROCEDURE CountRows
@TableName VARCHAR(30),
@TotalRows INT OUTPUT
AS
-- Attempt one: Error with the local variable
DECLARE @s VARCHAR(128)
SELECT @s = 'SELECT @TotalRows=Count(*) FROM ' + @TableName
EXEC (@s)
-- Attempt two: Error with @TableName
SELECT @TotalRows = (SELECT Count(*) FROM @TableName)
RETURN
GO
Is there a way I can dynamically set the table name and also have variables set? I'm using MS SQL Server 7 and Windows 2000." |
|
|
|
|
|
Dynamic table name