looping in a stored proc - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

looping in a stored proc

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-02 : 15:46:20

I have 12 tables (table1, table2, ... table12) and in my stored proc, I need to check whether each table is populated. I don't need to check them all, but the number of tables that should be checked depend on the number I pass to the stored procedure. If I pass 5, then only the first 5 tables must be checked. What should I return to my asp page? Perhaps return a string of true/false values joined by a character (say a % sign) and then parse the string in my asp page?

I need your help. Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-02 : 15:52:37

OK, here's the thing: what identifies 5 tables as being the "first" five, or the "last" five? Do the tables have numbers in their names that signify this?

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-02 : 15:54:55

table1 will be the first table and table12 will be the last.

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-02 : 16:06:59

Here's an attempt at it:

CREATE PROCEDURE FreeKeyOrNot
@numofcats int

AS

Declare @counter int, @retvalue varchar(50)
Set @counter = 1
Set @retvalue = ''

while @counter < @numofcats
begin
if exists ( Select lngCodeID from table1 )
Set @retvalue = @retvalue + '1'
else
Set @retvalue = @retvalue + '0'
Set @counter = @counter + 1
return @retvalue
end

I don't know how to turn the if statement to check table1, table2, etc. as it loops. Any idea?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-02 : 16:17:16

Actually, you don't even need to loop this, since the table names are relatively fixed, you can query the sysindexes table to get the row count:

CREATE PROCEDURE ShowRowCounts @maxtblID int AS
SELECT O.name, I.rowcnt
FROM sysindexes I INNER JOIN sysobjects O on I.id=O.id
WHERE I.indid < 2 AND O.xtype='U'
AND (o.name Like 'table[1-9]' OR o.name Like 'table1[012]')
AND Convert(int,SubString(o.name,6,99)) <= @maxtblID

This will select the tables from sysobjects and sysindexes and return the number of rows in each table.

Edited by - robvolk on 05/02/2002 16:18:52

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-02 : 16:29:43

Thanks for writing that stored proc for me. I forgot to mention that I also need to pass the server name and database name as the tables may reside on diff servers and DBs. How can I incorporate these info into that stored proc?

Thanks again.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-02 : 19:42:37

Are you saying that the fully-qualified table names would be something like:

Server1.db1.dbo.Table1
Server1.db5.dbo.Table2
Server4.db2.dbo.Table3

and so on? Then Rob's Sysindexes query won't work. You might be able to do a little Dynamic SQL or maybe just hard code some IF @numofcats > X lines.

Will the number of tables remain static, or will they also be changing?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources