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.
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.
ASKER
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
"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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
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
ASKER
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??
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??
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...
I don't see the problem with "my" suggestion, though...
ASKER
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.
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.
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
CREATE PROC myProc
@random bit = 0
AS
if @random=1
SELECT * FROM myTable
ORDER BY NEWID()
else
SELECT * FROM myTable
ORDER BY ID
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Not sure. Perhaps angelll can help you w/ that one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That makes sense. Thank you very much for your help.
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