Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    9

    Unanswered: stored procedure recordset looping

    I have searched high and low for examples of stored procedures.
    they are hard to find!
    What I need to do is loop through a recordset inside my sp.

    DECLARE @PlacePK int
    SET @PlacePK = 0
    WHILE @PlacePK IS NOT NULL
    BEGIN
    SET @PlacePK = (SELECT PlacePK FROM Place)
    INSERT INTO LimitsToPlace (PlaceFK,GuestKeyFK,DailyLimit,TotalLimit) VALUES ( @PlacePK,@GuestKeyPK,@LimitAmount, @LimitAmount )
    END

    the error I get here is @PlacePK cannot accept multiple returns
    This code doesnt seem right to me cause my "make rs" is inside the loop. I would think it should be something like
    WHILE(@PlacePK = (SELECT PlacePK FROM Place))

    does anyone have some code examples I can look at to see how to do recordset looping.

    more questions ...
    the EXEC() function is not clear to me and the ms help files dont help enough.
    can I do this?

    EXEC("UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = '" + @CardInternalNumberFK + "'")
    or just

    EXEC(UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = @CardInternalNumberFK)
    and if i can do the 2nd one and CardInternalNumber is a varchar do I have to add something like

    EXEC(UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = QUOTENAME(@CardInternalNumberFK))

    or do I have to build it as a var then exec the var
    DECLARE @SQL varchar(2000)
    SET @SQL = "UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = '" + @CardInternalNumberFK + "'"
    EXEC(@SQL)

    or I just use it straight as
    UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = @CardInternalNumberFK

    Lorddog (thank you for your time)

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    try:
    Code:
    DECLARE @PlacePK int
    select @PlacePK = min(PlacePK) FROM Place
    WHILE @PlacePK IS NOT NULL
    BEGIN
       INSERT INTO LimitsToPlace (PlaceFK,GuestKeyFK,DailyLimit,TotalLimit) VALUES ( @PlacePK,@GuestKeyPK,@LimitAmount, @LimitAmount )
       select @PlacePK = min(PlacePK) FROM Place where PlacePK > @PlacePK
    END
    as for the EXECUTE()

    UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = @CardInternalNumberFK

    should work without the EXECUTE(). if @CardInternalNumberFK is a char/varchar and CardInternalNumber is an int then try:

    UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = cast(@CardInternalNumberFK as int)
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Posts
    9

    Re: stored procedure recordset looping

    ok during the time I was searching around and I found cursors
    I tryied this code and I guess this is the way to go

    DECLARE @PlacePK int
    DECLARE Place_cursor CURSOR FOR
    SELECT PlacePK FROM Place
    OPEN Place_cursor
    FETCH NEXT FROM Place_cursor INTO @PlacePK
    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO LimitsToPlace (PlaceFK,GuestKeyFK,DailyLimit,TotalLimit) VALUES (@PlacePK,@GuestKeyPK,@LimitAmount,@LimitAmount)
    FETCH NEXT FROM Place_cursor INTO @PlacePK
    END
    CLOSE Place_cursor
    DEALLOCATE Place_cursor

    also @CardInternalNumber is a varchar because it has to have leading 0's
    -insidently this is a proxy card read from a device connected to a com port. Kind of a neat project.

    many thanks for your time.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Although a cursor would solve your problem, they are expensive to use. The example I provided is the less resource intensive alternative to a cursor. IMHO cursors should be avoided at all costs.

    My fisrt example on @CardInternalNumber would work provided @CardInternalNumber and CardInternalNumber are of the same type.

    I didn't consider that your question was more on EXECUTE() than the statment your were trying to execute. Did you need an example for EXECUTE()?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2003
    Posts
    9
    I have had no formal training so there are alot of things I dont (wish I did) know about what is going on with t-sql. although I have been using it to build 6 joins and substring parsing and stuff it is very helpful for you to point out reasons why to use commands this way or that.

    I will adopt back to your way of doing the looping. It is a very smart way to do it.

    about the execute I mainly was wondering why I couldnt build my string in the command.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    although I have work with Sybase, Ingres, Oracle and MSSQL Srvr I too have not had any formal training. I have had time to figure out what works.

    for the EXECUTE:

    Code:
    EXEC("UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = '" + @CardInternalNumberFK + "'")
    should have worked. I personnaly prefer the following

    Code:
    DECLARE @SQL varchar(2000), @Debug bit
    set @Debug = 1
    SET @SQL = "UPDATE CardNumber SET Status = 'Assigned' WHERE CardInternalNumber = '" + @CardInternalNumberFK + "'"
    if (@Debug > 0)
       raiserror('@SQL: %s',0,1,@SQL)
    else
       EXEC(@SQL)
    I generally use this in stored procedures. If I start to have a problem I can pass 1 for the @Debug parm and get diagnostics.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •