Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Dynamic Order By NewID() - is this approach correct?

I need to select some records from a table, and order them based on the value of a procedure argument (bit field).  Something like this:

CREATE PROC myProc
  @random bit = 0
AS
   SELECT * FROM myTable
   ORDER BY
   CASE WHEN @random = 1 THEN CAST(NEWID() AS SQL_VARIANT)
             ELSE ID
   END

Am I correct in casting NewID to type SQL_Varient.  If I just use NewID I get an error saying that "Implicit conversion from uniqueidentifier is not allowed".
Although the above seems to work I need to know I'm not setting myself up for a future disaster...!  Thanks.
0
Rouchie
Asked:
Rouchie
  • 6
  • 4
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should make it like this:

CREATE PROC myProc
  @random bit = 0
AS
   SELECT * FROM myTable
   ORDER BY CASE WHEN @random = 1 THEN NEWID() ELSE NULL END
     , CASE WHEN @random = 1 THEN NULL ELSE ELSE ID END
             
that will avoid any data type clash
0
 
RouchieAuthor Commented:
This gives an error:

"The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

Just to clarify,
If @random = 1 then I need to order by NEWID()
If @random = 0 then I need to order by ID
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should work:

CREATE PROC myProc
  @random bit = 0
AS
   SELECT * FROM myTable
   ORDER BY CASE WHEN @random = 1 THEN NEWID() ELSE NULL END
     , CASE WHEN @random = 1 THEN NULL ELSE ID END

otherwise:

CREATE PROC myProc
  @random bit = 0
AS
   SELECT * FROM myTable
   ORDER BY CASE WHEN @random = 1 THEN NEWID() ELSE NULL END
    , ID
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
RouchieAuthor Commented:
Still getting the same error for both your suggestions.  

Perhaps my actual SQL statement needs to be used in case i've made a mistake.

SELECT
  ab.answerID,
  qi.questionImageID
FROM
  tbl_MCAnswerBank ab LEFT OUTER JOIN
  tbl_QuestionImages qi ON ab.answerImage = qi.questionImageID
ORDER BY
  CASE WHEN @randomAnswers = 1 THEN NEWID() ELSE NULL END,
  ab.answerID

I think my original post worked.  Can you tell me why you don't recommend that approach please??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem with your original approach that casting to sql_Variant will make the int also go to sql_variant, and eventually resulting in a sort order you don't want...

I don't see the problem with "my" suggestion, though...
0
 
RouchieAuthor Commented:
Okay thanks for the clarification on the sql_variant type.  I had to ask just so I understand your answer rather than just paste it into my code.

Your new suggestion is still giving me the error.  I posted my code to see if there was anything in there that might cause it.
0
 
derekkrommCommented:
The problem is that you can't convert an int to a uniqueidentifier and vice versa.

CREATE PROC myProc
  @random bit = 0
AS
if @random=1
   SELECT * FROM myTable
   ORDER BY NEWID()
else
   SELECT * FROM myTable
   ORDER BY ID
0
 
RouchieAuthor Commented:
Hi derekkromm

Why does SQL think there is a conversion problem?  Only the int or the NewID would ever be used to sort - not both together, so I don't really grasp what's happening!

My original code was as you've suggested, but I hoped I could avoid all the duplicate code, hence the reason for the post.
0
 
derekkrommCommented:
What happens is when SQL Server parses the code, and it sees a case statement, it requires any resulting values to be of the same type. Typically this is done implicitly and we dont' see any errors/warnings. However, there are cases (like this one), where a conversion isn't possible between some datatypes.
0
 
RouchieAuthor Commented:
Ahh okay, that makes sense.  Thank you!

So going back to angelIII's proposed solution:

CREATE PROC myProc
  @random bit = 0
AS
   SELECT * FROM myTable
   ORDER BY CASE WHEN @random = 1 THEN NEWID() ELSE NULL END
     , CASE WHEN @random = 1 THEN NULL ELSE ID END

How come that doesn't work?
0
 
derekkrommCommented:
Not sure. Perhaps angelll can help you w/ that one.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
looks like the error message is misleading, and that you cannot have the NEWID() inside the order by case statement.

means, for NEWID() inside a CASE statement, we need to do this:

CREATE PROC myProc
  @random bit = 0
AS
   SELECT *
   FROM ( Select t.*, newid() x FROM myTable t
   ) l
   ORDER BY CASE WHEN @random = 1 THEN x ELSE NULL END
    , CASE WHEN @random = 1 THEN NULL ELSE ID END

0
 
RouchieAuthor Commented:
That makes sense.  Thank you very much for your help.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now