Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Dynamic ORDER.

Expand Messages
  • Kevin Collins
    You could always pass in a varchar param containing the column list to sort by, build the sql as in a string variable (just like you would in an ASP), append
    Message 1 of 6 , Mar 6, 2003
    • 0 Attachment
      You could always pass in a varchar param containing the column list to
      sort by, build the sql as in a string variable (just like you would in
      an ASP), append the column list after ORDER BY, and then execute the
      string.


      > -----Original Message-----
      > From: Iian Neill [mailto:ineill@...]
      > Sent: Thursday, March 06, 2003 9:49 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Dynamic ORDER.
      >
      >
      > How would one go about ordering results in a stored procedure in
      according
      > to a CASE or an IF statement? I have used CASE successfully when
      there's
      > only ONE column that requires ordering; if you need to arrange the
      results
      > by two or more columns, SQL Server protests. Apparently the CASE
      statement
      > is designed to only process one statement - or, in the case of ORDER
      BY -
      > one column.
      >
      > The ideal solution would be to have the ASP script pass a value
      > representing the order pattern; a CASE or IF statement would then sort
      the
      > columns accordingly. Can anyone think of a solution that does not
      resort
      > to
      > putting the entire SELECT into a series of IF statements? At present,
      in
      > order to simulate dynamic ordering I've had to replicate the procedure
      > (with modifications to the ORDER clause, naturally) across five other
      > procedures.
      >
      > This seems awfully inefficient.
      >
      > Any ideas?
      >
      > thanks,
      > Iian
      >
      >
      >
      >
      > ------------------------ Yahoo! Groups Sponsor
      >
      > To unsubscribe from this group, send an email to:
      > SQLQueriesNoCode-unsubscribe@yahoogroups.com
      >
      >
      >
      > Your use of Yahoo! Groups is subject to
      http://docs.yahoo.com/info/terms/
    • Greg Banse
      This is the only way I ve found to do it that s pre-compiled. But if any one has a better way, I would love to know also. CREATE PROCEDURE procname @case int,
      Message 2 of 6 , Mar 7, 2003
      • 0 Attachment
        This is the only way I've found to do it that's pre-compiled. But if any one
        has a better way, I would love to know also.

        CREATE PROCEDURE procname
        @case int,
        AS

        IF @case = 1 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f1 END
        IF @case = 2 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f2 END
        IF @case = 3 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f3 END
        Etc.

        Seems to be working fine for me.

        Greg

        -----Original Message-----
        From: Iian Neill [mailto:ineill@...]
        Sent: Thursday, March 06, 2003 7:49 AM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Dynamic ORDER.



        How would one go about ordering results in a stored procedure in according
        to a CASE or an IF statement? I have used CASE successfully when there's
        only ONE column that requires ordering; if you need to arrange the results
        by two or more columns, SQL Server protests. Apparently the CASE statement
        is designed to only process one statement - or, in the case of ORDER BY -
        one column.

        The ideal solution would be to have the ASP script pass a value
        representing the order pattern; a CASE or IF statement would then sort the
        columns accordingly. Can anyone think of a solution that does not resort to
        putting the entire SELECT into a series of IF statements? At present, in
        order to simulate dynamic ordering I've had to replicate the procedure
        (with modifications to the ORDER clause, naturally) across five other
        procedures.

        This seems awfully inefficient.

        Any ideas?

        thanks,
        Iian




        ------------------------ Yahoo! Groups Sponsor ---------------------~--> New
        Yahoo! Mail Plus. More flexibility. More control. More power. Get POP
        access, more storage, more filters, and more.
        http://us.click.yahoo.com/Hcb0iA/P.iFAA/46VHAA/m7folB/TM
        ---------------------------------------------------------------------~->

        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
      • Terri Morton
        SELECT f1, f2, f3 FROM table ORDER BY CASE @case WHEN 1 THEN f1 END, CASE @case WHEN 2 THEN f2 END, CASE @case WHEN 3 THEN f3 END Terri ... From: Greg Banse
        Message 3 of 6 , Mar 7, 2003
        • 0 Attachment
          SELECT f1, f2, f3 FROM table
          ORDER BY
          CASE @case
          WHEN 1 THEN f1
          END,
          CASE @case
          WHEN 2 THEN f2
          END,
          CASE @case
          WHEN 3 THEN f3
          END

          Terri

          -----Original Message-----
          From: Greg Banse [mailto:gbanse@...]
          Sent: Friday, March 07, 2003 10:19 AM


          This is the only way I've found to do it that's pre-compiled. But if any one
          has a better way, I would love to know also.

          CREATE PROCEDURE procname
          @case int,
          AS

          IF @case = 1 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f1 END
          IF @case = 2 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f2 END
          IF @case = 3 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f3 END
          Etc.

          Seems to be working fine for me.
        • Greg Banse
          Yes...for this example...my code does much more than a simple order. And its doing Update statements, not Select statements. Sorry I simplified my example a
          Message 4 of 6 , Mar 7, 2003
          • 0 Attachment
            Yes...for this example...my code does much more than a simple order. And its
            doing Update statements, not Select statements. Sorry I simplified my
            example a little too much.

            Greg


            -----Original Message-----
            From: Terri Morton [mailto:tmorton@...]
            Sent: Friday, March 07, 2003 1:00 PM
            To: 'SQLQueriesNoCode@yahoogroups.com'
            Subject: RE: [SQLQueriesNoCode] Dynamic ORDER.



            SELECT f1, f2, f3 FROM table
            ORDER BY
            CASE @case
            WHEN 1 THEN f1
            END,
            CASE @case
            WHEN 2 THEN f2
            END,
            CASE @case
            WHEN 3 THEN f3
            END

            Terri

            -----Original Message-----
            From: Greg Banse [mailto:gbanse@...]
            Sent: Friday, March 07, 2003 10:19 AM


            This is the only way I've found to do it that's pre-compiled. But if any one
            has a better way, I would love to know also.

            CREATE PROCEDURE procname
            @case int,
            AS

            IF @case = 1 BEGIN SELECT f1, f2, f3 FROM table ORDER BY f1 END IF @case = 2
            BEGIN SELECT f1, f2, f3 FROM table ORDER BY f2 END IF @case = 3 BEGIN SELECT
            f1, f2, f3 FROM table ORDER BY f3 END Etc.

            Seems to be working fine for me.


            ------------------------ Yahoo! Groups Sponsor ---------------------~--> Get
            128 Bit SSL Encryption!
            http://us.click.yahoo.com/xaxhjB/hdqFAA/xGHJAA/m7folB/TM
            ---------------------------------------------------------------------~->

            To unsubscribe from this group, send an email to:
            SQLQueriesNoCode-unsubscribe@yahoogroups.com



            Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
          • Terri Morton
            Iian -- I believe your problem is that you wanted to conditionally ORDER BY multiple columns. Adding to my first reply, what you d need is something like
            Message 5 of 6 , Mar 7, 2003
            • 0 Attachment
              Iian -- I believe your problem is that you wanted to conditionally ORDER BY
              multiple columns. Adding to my first reply, what you'd need is something
              like this:

              SELECT f1, f2, f3 FROM table
              ORDER BY
              CASE @case
              WHEN 1 THEN f1
              END,
              CASE @case
              WHEN 1 THEN f2
              END,
              CASE @case
              WHEN 2 THEN f2
              END,
              CASE @case
              WHEN 2 THEN f1
              END,
              CASE @case
              WHEN 3 THEN f3
              END

              The results are:
              when @case is 1, will order by f1, f2
              when @case is 2, will order by f2, f1
              when @case is 3, will order by f3

              Terri


              -----Original Message-----
              From: Greg Banse [mailto:gbanse@...]
              Sent: Friday, March 07, 2003 3:34 PM


              Yes...for this example...my code does much more than a simple order. And its
              doing Update statements, not Select statements. Sorry I simplified my
              example a little too much.

              Greg


              -----Original Message-----
              From: Terri Morton [mailto:tmorton@...]
              Sent: Friday, March 07, 2003 1:00 PM


              SELECT f1, f2, f3 FROM table
              ORDER BY
              CASE @case
              WHEN 1 THEN f1
              END,
              CASE @case
              WHEN 2 THEN f2
              END,
              CASE @case
              WHEN 3 THEN f3
              END

              Terri
            Your message has been successfully submitted and would be delivered to recipients shortly.