Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 looping in a stored proc
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PeterG
Posting Yak Master

Canada
156 Posts

Posted - 05/02/2002 :  15:46:20  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 05/02/2002 :  15:52:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Canada
156 Posts

Posted - 05/02/2002 :  15:54:55  Show Profile  Reply with Quote
table1 will be the first table and table12 will be the last.

Go to Top of Page

PeterG
Posting Yak Master

Canada
156 Posts

Posted - 05/02/2002 :  16:06:59  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 05/02/2002 :  16:17:16  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Canada
156 Posts

Posted - 05/02/2002 :  16:29:43  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 05/02/2002 :  19:42:37  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000