calling a stored procedure in a while loop in ms sql

So it seems like I don't really have to look into the Information
shcema for TestData6061 as I mentioned earlier in the code. Thosee 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
sync
...
...
...
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

well basically I want to access the output of that sql query, one by one, thats what I meant by single value, sorry if I confused you or anything.
As I mentioned I want to be able to access 'accounting', 'fundsturctures' etc from the output of the query

So the above cursor actually works but the problem is it works only when I run the query through 'New Query' on the GUI n it shows me a list of all the object classes like accntg, calendar etc.
But If I just open Schema or SchemaFields table and then click on 'Show SQL Pane' n execute it over there, it just shows me 'Query has executed successfully' but doesn't show anything in the output
I am attaching a file showing the print screen when run through the 'sql pane' for schema table and the other one used with 'New Query' on the MS SQL GUI which works fine

The Idea is to run a query (using functions/stored procedure etc) which will be of the form
EXECUTE <sp_name> 'TestData6061', 'accounting', 133
accounting ist going to be changing.
So I want to have a loop in which I just execute the above stored procedure and assign value to the middle parameter dynamically depending on the value got from the cursor (defined by chapmandew). Thanks in advance

So I tried to call my stored procedure (not written by me, my manager wrote it and supposed to be right) as you mentioned as below
EXECUTE spLoaderCoverageObjectTypeActionType 'TestData6061', @ObjectClass, 136
but I just see the results for 'accounting' whereas it should be showing 'accounting', 'fundstructures', 'transaction' etc (all object classes)

Here is the code for the stored procedure for you

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spLoaderCoverageObjectTypeActionType]
      @sDatabaseName sysname,
    @sTableName sysname,
    @iSchemaID int
AS
BEGIN
      SET NOCOUNT ON;
   EXEC ('SELECT     SchemaQuery.ObjectClass, SchemaQuery.ObjectType, SchemaQuery.ActionType, DataQuery.Count
           FROM  
             (SELECT DISTINCT ObjectClass, ObjectType, ''New'' AS ActionType
              FROM          SchemaFields
              WHERE      SchemaID = ' + @iSchemaID + ' AND ObjectClass LIKE ''%'+ @sTableName +'%'') AS SchemaQuery LEFT OUTER JOIN
                          (SELECT     TypesQuery.ObjectClass, TypesQuery.ObjectType, COUNT(*) AS Count
                            FROM          (SELECT DISTINCT ObjectClass, ObjectType
                                           FROM          SchemaFields AS SchemaFields_1
                                           WHERE      (SchemaID = ' + @iSchemaID + ')) AS TypesQuery INNER JOIN
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + ' ON TypesQuery.ObjectType = ' + @sDatabaseName + '.dbo.' + @sTableName + '.[01-RecordClass] AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.AutomationType = ''Loader'' AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.[Negative Testcase] = 0 AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.[02-RecordAction] = ''New''
                            GROUP BY TypesQuery.ObjectClass, TypesQuery.ObjectType) AS DataQuery ON SchemaQuery.ObjectClass = DataQuery.ObjectClass AND SchemaQuery.ObjectType = DataQuery.ObjectType
           UNION
           SELECT     SchemaQuery.ObjectClass, SchemaQuery.ObjectType, SchemaQuery.ActionType, DataQuery.Count
           FROM  
             (SELECT DISTINCT ObjectClass, ObjectType, ''Update'' AS ActionType
              FROM          SchemaFields
              WHERE      SchemaID = ' + @iSchemaID + ' AND ObjectClass LIKE ''%'+ @sTableName +'%'') AS SchemaQuery LEFT OUTER JOIN
                          (SELECT     TypesQuery.ObjectClass, TypesQuery.ObjectType, COUNT(*) AS Count
                            FROM          (SELECT DISTINCT ObjectClass, ObjectType
                                           FROM          SchemaFields AS SchemaFields_1
                                           WHERE      (SchemaID = ' + @iSchemaID + ')) AS TypesQuery INNER JOIN
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + ' ON TypesQuery.ObjectType = ' + @sDatabaseName + '.dbo.' + @sTableName + '.[01-RecordClass] AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.AutomationType = ''Loader'' AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.[Negative Testcase] = 0 AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.[02-RecordAction] = ''Update''
                            GROUP BY TypesQuery.ObjectClass, TypesQuery.ObjectType) AS DataQuery ON SchemaQuery.ObjectClass = DataQuery.ObjectClass AND SchemaQuery.ObjectType = DataQuery.ObjectType  
           UNION
           SELECT     SchemaQuery.ObjectClass, SchemaQuery.ObjectType, SchemaQuery.ActionType, DataQuery.Count
           FROM  
             (SELECT DISTINCT ObjectClass, ObjectType, ''Delete'' AS ActionType
              FROM          SchemaFields
              WHERE      SchemaID = ' + @iSchemaID + ' AND ObjectClass LIKE ''%'+ @sTableName +'%'') AS SchemaQuery LEFT OUTER JOIN
                          (SELECT     TypesQuery.ObjectClass, TypesQuery.ObjectType, COUNT(*) AS Count
                            FROM          (SELECT DISTINCT ObjectClass, ObjectType
                                           FROM          SchemaFields AS SchemaFields_1
                                           WHERE      (SchemaID = ' + @iSchemaID + ')) AS TypesQuery INNER JOIN
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + ' ON TypesQuery.ObjectType = ' + @sDatabaseName + '.dbo.' + @sTableName + '.[01-RecordClass] AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.AutomationType = ''Loader'' AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.[Negative Testcase] = 0 AND
                                                   ' + @sDatabaseName + '.dbo.' + @sTableName + '.[02-RecordAction] = ''Delete''
                            GROUP BY TypesQuery.ObjectClass, TypesQuery.ObjectType) AS DataQuery ON SchemaQuery.ObjectClass = DataQuery.ObjectClass AND SchemaQuery.ObjectType = DataQuery.ObjectType  

            ORDER BY  SchemaQuery.ObjectClass,  SchemaQuery.ActionType, SchemaQuery.ObjectType
')
END

yup, but it returned only the accounting. if its called in the loop, It should be be returning all the object classes

what exactly is the difference between running sql query from a 'sql pane' for a table and clicking on 'New Query' on the MS SQL GUI n executing the same query. I get different results. Pleaset let me know if you know anything about it. Thanks a million

it works but the problem is that we have a output with indvidual results as shows in the attachment, Basically I want to have an output which will consolidate all the results of the cursor into a single table output form.

Here the output when the cursor is ran but like I said basically I want to have the a consolidated output into a single table. Is there anyway to do that? Thanks in advance

Okay, NP. Thanks chapmandew. I really appreciate your help with this one. Thanks a lot again. I have filed another one which says 'joining/transforming' output of a cursor into a single table.