Posted by Jozef de Veuster on 07/25/05 16:14

Hi,

I'm trying to create a Stored Procedure that returns a recordset, but I
want to be able to choose the ORDER BY clause in mijn parameter list of
the Stored Procedure. Since CASE .. WHEN can only be used in the SELECT
clause, I came up with the following:

-- BEGIN SCRIPT --
DECLARE @blah AS VARCHAR(20)
SET @blah = 'DOSSIER_CODE'
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @blah
WHEN 'DOSSIER_CODE'
THEN DOSSIER_CODE
WHEN 'SCAN_DATE'
THEN SCAN_DATE
ELSE
SCAN_DATE
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON MR_DOCS.USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE MR_DOCS.USER_FID = 1
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
-- Added by Tim Derdelinckx - 2005.06.20
AND TODO_FID IS NULL
-- Select documents that are scanned for this user (1),
-- or moved to this user (3),
-- or forwarded to this user (4),
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
UNION
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @blah
WHEN 'DOSSIER_CODE'
THEN DOSSIER_CODE
WHEN 'SCAN_DATE'
THEN SCAN_DATE
ELSE
SCAN_DATE
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE BORROW_USER_FID = 1
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
-- Added by Tim Derdelinckx - 2005.06.20
AND TODO_FID IS NULL
-- or borrowed to this user
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
ORDER BY ORDERFIELD DESC
-- END SCRIPT --

But it doesn't seem to work correctly:
When SET @blah = 'SCAN_DATE', it works just fine!

When SET @blah = 'DOSSIER_CODE':
I get an error: Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET
operation.

Anyone any ideas about this? Or maybe another way of handling this (not
with CASE .. WHEN)?

Thanks a lot,
Tim@Allgeier

*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]