complicated sql and hard to read Thanks for any help -- mfg Marc Eggenberger [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Marc [ip] => marc.eggenberge [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> Order by based on a variable - Microsoft SQL / MS SQL Server

Order by based on a variable - Microsoft SQL / MS SQL Server

Hi there. I pass an SortColumn to a stored procedure. I found that I either need: a. to use dynamic sql (string with the sql and then exec(cmdSQL) b. use case When I use a I have a permission problem. All my tables are not directly accessable for the users. They can only use views and stored procedures. This works fine until I use exec() in a stored procedure. I get access denied's. Is the dynamic sql executed with the level of the calling user? The normal sp does not. Right? Can I change this behavoir? Or do I have ...

  1. #1

    Default Order by based on a variable

    Hi there.

    I pass an SortColumn to a stored procedure.
    I found that I either need:

    a. to use dynamic sql (string with the sql and then exec(cmdSQL)
    b. use case

    When I use a I have a permission problem. All my tables are not directly
    accessable for the users. They can only use views and stored procedures.
    This works fine until I use exec() in a stored procedure. I get access
    denied's. Is the dynamic sql executed with the level of the calling
    user? The normal sp does not. Right? Can I change this behavoir?

    Or do I have to use b?
    Problem here is that the columns are dynamic, so I have to have a case
    for every possible columns which are quite a few --> complicated sql and
    hard to read

    Thanks for any help

    --
    mfg
    Marc Eggenberger
    Marc Guest

  2. #2

    Default Re: Order by based on a variable

    In article <#phx.gbl>, si
    says... 

    This only works when all fields are of the same type .. else I have to
    write

    select * from TableA
    order by
    (
    case OBCN
    WHEN 1 then field1
    WHEN 2 then field2
    ELSE NULL
    END ASC,
    case OBCN
    WHEN 3 then field3
    WHEN 4 then field4
    ELSE NULL
    END ASC
    )

    And this example is only with 4 columns and 2 different datatypes ...
    I have about 25 columns and 5 different datatypes.
    Its doable .. I agree, but its not "nice".


    --
    mfg
    Marc Eggenberger
    Marc Guest

  3. #3

    Default Re: Order by based on a variable

    Hi Marc,

    If you have 25 different columns on which you want to allow your users to be
    able to sort, it is probably better for your system to do the sorting on the
    client side, specially if the table(s) you are selecting from have data
    inserted/updated/deleted as well as selected, because creating indexes that
    will help all your possible sorts will slow down all the
    inserts/update/deletes.

    Select the data unsorted from the database and load it in a client side
    control that aloows you to sort. That also has the advantage that if the
    user wants to sort the data in a different order (not unlikely if there are
    so many columns to order by), the sorting will happen on the client, and
    there is not another roundtrip to the database to get the data in a
    different sort order.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Marc Eggenberger" <alstom.com> wrote in message
    news:cacti.ch.alstom.com... [/ref]
    just 
    >
    > This only works when all fields are of the same type .. else I have to
    > write
    >
    > select * from TableA
    > order by
    > (
    > case OBCN
    > WHEN 1 then field1
    > WHEN 2 then field2
    > ELSE NULL
    > END ASC,
    > case OBCN
    > WHEN 3 then field3
    > WHEN 4 then field4
    > ELSE NULL
    > END ASC
    > )
    >
    > And this example is only with 4 columns and 2 different datatypes ...
    > I have about 25 columns and 5 different datatypes.
    > Its doable .. I agree, but its not "nice".
    >
    >
    > --
    > mfg
    > Marc Eggenberger[/ref]


    Jacco Guest

  4. #4

    Default Re: Order by based on a variable

    Marc,

    You don't have to worry about the types if you list each
    column separately:

    ....
    order by
    case when Order_By_Column_Name = 1 then Field1 end,
    case when Order_By_Column_Name = 2 then Field2 end,
    ....

    If you have small result sets and don't care whether indexes are
    used for the ordering, you can force the common type of the
    case expression to sql_variant:

    order by case Order_By_Column_Name
    when 1 then cast(Field1 as sql_variant)
    when 2 then Field2
    when 3 then Field3
    ....

    -- Steve Kass
    -- Drew University
    -- Ref: 08E52862-5727-4770-AD75-AA93DFC291DE


    Marc Eggenberger wrote:
     
    >
    >This only works when all fields are of the same type .. else I have to
    >write
    >
    >select * from TableA
    >order by
    >(
    > case OBCN
    > WHEN 1 then field1
    > WHEN 2 then field2
    > ELSE NULL
    > END ASC,
    > case OBCN
    > WHEN 3 then field3
    > WHEN 4 then field4
    > ELSE NULL
    > END ASC
    >)
    >
    >And this example is only with 4 columns and 2 different datatypes ...
    >I have about 25 columns and 5 different datatypes.
    >Its doable .. I agree, but its not "nice".
    >
    >
    >
    >[/ref]

    Steve Guest

Similar Threads

  1. Replies: 3
    Last Post: July 9th, 04:19 PM
  2. Sorting a query based on URL variable
    By eddymilner in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 9th, 06:57 PM
  3. Order by certain variable
    By OldCFer in forum Macromedia ColdFusion
    Replies: 2
    Last Post: February 17th, 07:08 PM
  4. HELP: variable increment based on date
    By Patrick Lee Humphrey in forum PHP Development
    Replies: 0
    Last Post: September 12th, 10:45 AM
  5. Replies: 1
    Last Post: August 6th, 11:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139