Order By Error

• david.buncle

  Right there with Babe

  Points: 774

  More actions

  October 10, 2008 at 4:30 am

  #194478

  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.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  October 10, 2008 at 4:57 am

  #883338

  You order by single column.

  But you put into it values from different ones depending on parameter.

  SQL Server converts all values in all columns to the same datatype according to its rules of precedence.

  Because one of he columns is int all others are converted to int.

  _____________
  Code for TallyGenerator