Hi All.
I have a problem here where I send a bunch of parameters to a stored procedure to return a dataset. The problem is with the ORDER BY clause.... I send it an integer value and depending on the number it is supposed to order by that field... It orders properly when I specify CASE_UI (@ORDER_BY=1), however when I choose to order by SURNAME (@ORDER_BY=2) I get at error:
Error converting data type varchar to numeric.
SELECT CASE_UID, SURNAME,FORENAME, DOB,POSTCODE,NHS_NUMBER,GENDER,STATUS
FROM dbo.vwAllPatients
WHERE (dbo.vwAllPatients.SURNAME LIKE ISNULL(@SURNAME, dbo.vwAllPatients.SURNAME) + '%') AND
(dbo.vwAllPatients.DOB LIKE ISNULL(@DOB, dbo.vwAllPatients.DOB) + '%') AND
(dbo.vwAllPatients.POSTCODE LIKE ISNULL(@POSTCODE, dbo.vwAllPatients.POSTCODE) + '%') AND
(dbo.vwAllPatients.NHS_NUMBER LIKE ISNULL(@NHS_NUMBER, dbo.vwAllPatients.NHS_NUMBER) + '%') AND
(dbo.vwAllPatients.STATUS = ISNULL(@STATUS, dbo.vwAllPatients.STATUS)) AND
(dbo.vwAllPatients.GENDER = ISNULL(@GENDER, dbo.vwAllPatients.GENDER) )
ORDER BY CASE @ORDER_BY
WHEN 1 THEN dbo.vwAllPatients.CASE_UID
WHEN 2 THEN dbo.vwAllPatients.SURNAME
WHEN 3 THEN dbo.vwAllPatients.NHS_NUMBER
WHEN 4 THEN dbo.vwAllPatients.POSTCODE
WHEN 5 THEN dbo.vwAllPatients.STREET_NAME
WHEN 6 THEN dbo.vwAllPatients.DOB
ELSE dbo.vwAllPatients.SURNAME
END
Can anyone explain why this may be?
Cheers.