I am trying to call a stored procedure in a while loop but for some
reason it only returns 1 row whereas I am expectint 12 rows
Here is the stored procedure and the call

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[SP_FETCH_ROWS](@num int)
AS

SELECT Table_Name from TestData6061.INFORMATION_SCHEMA.Tables a where
@num =(select count(*) from TestData6061.INFORMATION_SCHEMA.Tables b
where a.Table_Name >= b.Table_Name)
go

While Loop which calls the stored procedure and passing the loop
counter as the argument
DECLARE @Count int DECLARE @MaxCount int
SET @Count = 0
SET @MaxCount = 29 WHILE @Count < @MaxCount BEGIN
SET @Count = @Count + 1 IF @Count = 1 OR
@Count = 4 OR
@Count = 7 OR
@Count = 8 OR
@Count = 14 OR
@Count = 17 OR
@Count = 18 OR
@Count = 22 OR
@Count = 25 OR
@Count = 27 OR
@Count = 28 OR
@Count = 29 EXEC releases.dbo.SP_FETCH_ROWS
@Count END

what am I missing here? I tried to figure out n looked for all kinds
of online documentation but still couldn't find the solution. please
helop

I just tested your code on one of my test databases and it retuned 12 rows
(but I have have over 200 tables there, and permissions to all of them).

A few things to check:

* How many tables do you have in the TestData6061 database? You need to have
at least 29 to see 12 rows as result.
* Do you have permissions to the tables in the TestData6061 database? The
information schema views let you see only objects for which you have
permissions.

Also, it just seems a bit odd that your SP is in the Releases database but
references the information schema views in the TestData6061 database.

Try to run the query below. Essentially it is equivalent to your stored
procedure and the loop combined together in one query. You can comment out
the WHERE clause to see all tables. If you are on SQL Server 2005 you can
replace the subquery calculating the seq column using ROW_NUMBER, like
ROW_NUMBER() OVER(ORDER BY table_name).

SELECT table_name, seq
FROM (
SELECT table_name,
(SELECT COUNT(*)
FROM TestData6061.INFORMATION_SCHEMA.Tables AS B
WHERE A.table_name >= B.table_name)
FROM TestData6061.INFORMATION_SCHEMA.Tables AS A)
AS T(table_name, seq)
WHERE seq IN (1, 4, 7, 8, 14, 17, 18, 22, 25, 27, 28, 29)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Feb 22, 7:21 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:

So it seems like I don't really have to look into the Information
shcema for TestData6061, these table names can be accessed as

SELECT DISTINCT ObjectClass
FROM SchemaFields
ORDER BY ObjectClass
and that will give me something like this ->
accounting
fundstructures
investment
party
price
reference
sync
systemsettings
transaction
twr

Now coming back to the looping, I basically want to have a sql query
which will loop through the above list (accounting, investment, etc
etc) giving me the single value as it goes through the loop. Thanks in
advance

On Sat, 23 Feb 2008 11:04:52 -0800 (PST), Vic wrote:

(snip)
Hi Vic,

Why? SQL Server is optimized for set-based operations. There are some
casees where iterating over a result set and processing rows one by one
is better, but they are seldom. If you can explain what you try to
achieve, we might be able to point you towards a better way.

Anyway, if you insist on doing this, read up on cursors in Books Online.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Feb 23, 4:31 pm, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:
So basically I am supposed to call a stored procedure (written by my
boss) as follows
EXECUTE spLoaderCoverageObjectTypeActionType 'TestData6061',
'accounting', 133
out of which 'accounting' is dynamic.

SELECT DISTINCT ObjectClass
FROM SchemaFields
order by ObjectClass
gives me the list of values that go in the middle argument which are
'accounting', 'calendar' etc etc.
So Idea is to call that stored proc in a cursor in a while loop to
give me. Here is the code for the cursor that I was working on
DECLARE @ObjectClass VARCHAR(200) DECLARE CursorTemplate CURSOR
FAST_FORWARD FOR SELECT DISTINCT ObjectClass

FROM releases.dbo.SchemaFields

ORDER BY ObjectClass OPEN CursorTemplate FETCH NEXT
FROM CursorTemplate
INTO @ObjectClass WHILE (@@FETCH_STATUS = 0) BEGIN EXECUTE
releases.dbo.spLoaderCoverageObjectTypeActionType 'TestData6061',
@ObjectClass, 136 FETCH NEXT
FROM CursorTemplate
INTO @ObjectClass END CLOSE CursorTemplate DEALLOCATE
CursorTemplate
but it doesn't give me the results for all the object classes, I just
get the results for 'accounting' when I run the above query in the
'sql pane' for SchemaFields table. If I go to 'New Query' on the MS
SQL GUI n start a new query analyzer n execute it over there I see
results. Why is it like that?

I have not used the 'SQL pane', but my understanding is that it is intended
for writing simple queries against a table, and not for complex procedural
code. Running the code in a 'Query' window is the correct way.

HTH,

Plamen Ratchev
http://www.SQLStudio.com