Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your problem is the use of constants in the query. You are sorting by constant strings, rather than columns. I think you are mixing up dynamic SQL and regular SQL.</p> <p>To get the names, you would want to use:</p> <pre><code>SELECT ROW_NUMBER() OVER (Order by CASE WHEN @sortBy='datecreate' THEN datecreate WHEN @sortBy='id' THEN id WHEN @sortBy='DisplayName' THEN DisplayName END, </code></pre> <p>However, this won't work, because of the type conflict. You have two options. First, either convert all the types to string with the right sort order (YYYY-MM-DD for dates, zero pads for numbers). Or, since this is a stored procedure, create a separate query for each one.</p> <p>The issue is that you might get everything as a string, but I'm still not sure how to get the ASC and DESC in. I think you will need two queries for this, which is feasible since this is inside a stored procedure.</p> <p>I like Aaron's answer, but I think it can be improved.</p> <pre><code>WITH Ordering AS ( SELECT ROW_NUMBER() OVER (ORDER BY (case when @sortBy = 'datecreate' and @sortdirection = 'ASC' then datecreate end), (case when @sortBy = 'datecreate' and @sortdirection = 'DESC' then datecreate end) desc, (case when @sortBy = 'id' and @sortdirection = 'ASC' then id end), (case when @sortBy = 'id' and @sortdirection = 'DESC' then id end) desc, (case when @sortBy = 'DisplayName' and @sortdirection = 'ASC' then DisplayName end), (case when @sortBy = 'DisplayName' and @sortdirection = 'DESC' then DisplayName end) desc ) as rn, dbo.x1.*, dbo.x2.* FROM dbo.x1 INNER JOIN dbo.x2 ON dbo.x1.type = dbo.x2.type where username = @username ) </code></pre> <p>And so on. The difference is that there is only one row_number() call, with a more complicated ordering expression. All the values will be NULL except for the one actually being used for the sort.</p>
    singulars
    1. This table or related slice is empty.
    1. POSorting a row limited SQL query with stored procedures
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGordon Linoff
    1. USGordon Linoff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COASC/DESC can be handled something like this. I'm sure you can find more details elsewhere. ORDER BY CASE WHEN @@sortDirection = 'ASC' CASE @@sortBy WHEN 'X' THEN X WHEN 'Y' THEN Y WHEN 'Z' THEN Z END ELSE NULL END ASC, CASE WHEN @@sortDirection = 'DESC' CASE @@sortBy WHEN 'X' THEN X WHEN 'Y' THEN Y WHEN 'Z' THEN Z END ELSE NULL END DESC
      singulars
      1. PO
      1. USshawnt00
    2. COYes . . . that is a good idea. Cumbersome, but clever.
      singulars
      1. PO
      1. USGordon Linoff
    3. CO@GordonLinoff I agree that limiting the sorting is preferential, but as the permutations go up, the maintainability of that query is an absolute nightmare. In fact the point of me making it so elaborate is to show how, in this case at least, dynamic SQL is probably the better option - both for maintainability and performance.
      singulars
      1. PO
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload