patternsqlMinor

Scramble the data output of a stored procedure

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

storedtheoutputscrambleproceduredata

Problem

I have a stored procedure that produces data for a call center system every morning. However, the data is sent in the same order daily. Is there a way to send the day in a different order every day without having to manually change the where clause? For examples today's data is : 1,2,3 tomorrow's will be :3,2,1 the day after 2,1,3 .. Same data different order daily.

Solution

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples:

-- Fully random ordering
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    NEWID() ;

-- Random ordering of results but the results still preserve the order of UserId
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    F.UserId
    , NEWID() ;

Another way, which will not produce all possible orderings but only a limited set but can be more efficient with large tables because it doesn't use NEWID(), only a single value for @aRandomNumber which should be drawn from the range of the column that is compared against (F.UserId here) :

WITH cte AS
  ( SELECT *, 0 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE (  )
      AND F.UserId >= @aRandomNumber
    ORDER BY F.UserId
  UNION ALL
    SELECT *, 1 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE (  )
      AND F.UserId 
FROM cte
ORDER BY ord, UserId ;

Code Snippets

-- Fully random ordering
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    NEWID() ;

-- Random ordering of results but the results still preserve the order of UserId
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    F.UserId
    , NEWID() ;

WITH cte AS
  ( SELECT *, 0 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId >= @aRandomNumber
    ORDER BY F.UserId
  UNION ALL
    SELECT *, 1 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId < @aRandomNumber
    ORDER BY F.UserId
  )
SELECT <list of columns except the "ord">
FROM cte
ORDER BY ord, UserId ;

Context

StackExchange Database Administrators Q#112422, answer score: 5

Revisions (0)

No revisions yet.