Re: calling a stored procedure in a while loop

On Feb 23, 4:31 pm, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Sat, 23 Feb 2008 11:04:52 -0800 (PST), Vic wrote:

(snip)

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

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

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?


.

Relevant Pages

  • Re: sql query which has got 3 dynamic parameters
    ... is 'accounting', 'fundstructures', 'calendar' etc and thats what the ... Insert into SchemaFields (SchemaID, ObjectClass,ObjectType,FieldName, ... SELECT DISTINCT ObjectClass ... I did some research and quickly wrote a cursor and tried to execute ...
    (comp.databases.ms-sqlserver)
  • Re: sql query which has got 3 dynamic parameters
    ... DECLARE @ObjectClass VARCHARDECLARE CursorTemplate CURSOR ... FAST_FORWARD FOR SELECT DISTINCT ObjectClass ... ORDER BY ObjectClass OPEN CursorTemplate FETCH NEXT ...
    (comp.databases.ms-sqlserver)