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?

Go to Top of Page

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.

Go to Top of Page

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?

Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page

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?

Go to Top of Page
   

- Advertisement -