Swapping values of variables

  • Comments posted to this topic are about the item Swapping values of variables

    God is real, unless declared integer.

  • Thanks for this very instructive question. I didn't realize that the swap works in UPDATE statements - if that had been the question of the day, I would have gotten it wrong.

    -- webrunner

    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Back to the basics question - love it!

  • Great question and even better explanation, Thomas.  Nicely done.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I use this feature a lot, see the following example:

    declare @t table (i int identity primary key, name sysname)
    declare @i int = 1
    ,@name sysname

    insert into @t
    select TOP(10) name
    from sys.objects
    ORDER BY name

    while 1=1
    select @name = name
    ,@i += 1
    from @t
    where i = @i
    if @@ERROR != 0 OR @@ROWCOUNT < 1
    print @name

    P.S. In the UPDATE of your explanation, the swap runs as expected because the source (deleted) and the destination (inserted) are different, so, after the assignement the source fields do not change. Instead, the variables are both source and destination.

    DECLARE @i INT = 1
    , @j INT = 2

    CREATE TABLE #tmp (i INT, j INT)
    INSERT INTO #tmp (i, j) VALUES (1, 2)

    UPDATE #tmp
    SET @i = @j
    , @j = @i

    SELECT @i AS i, @j AS j


Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply