Dynamic Order Clause in a stored procedure on a non-numeric column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Order Clause in a stored procedure on a non-numeric column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-09 : 00:47:18

Joel writes "OS: Win 2000 ad. Server
db: SQL 2000

I am attempting to create a dynamic order clause in a stored procedure.

I followed Garth's example on how to create a Dynamic "order by" clause in a stored procedure, however I get the error message
"Syntax error converting datetime from character string" when I attempt to order by a non-numeric column. I.E. If I order by "DateOfBirth" (which is a DateTime), it works fine. If I order by "Name", I get the error.

Here's my stored proc... maybe I'm missing something.

CREATE procedure usp_PatientSearch 
	@PatientName VarChar (30),
	@PatientSSN VarChar(9),
	@SearchOrder Int
AS
	Begin
	if (@PatientSSN= "")
		select FullName, pkPatient, FMPSSN, DOB, LastBranchOfService 
		from tblPatient
		where tblPatient.FullName like @PatientName + '%'
		order by case 
			when @SearchOrder = 1 then FullName
			when @SearchOrder = 2 then FMPSSN
			when @SearchOrder = 3 then DOB
			else FullName
		end desc
	else
		select FullName, pkPatient, FMPSSN, DOB, LastBranchOfService 
		from tblPatient
		where tblPatient.PatientSSN like @PatientSSN + '%'
		order by case 
			when @SearchOrder = 1 then FullName
			when @SearchOrder = 2 then FMPSSN
			when @SearchOrder = 3 then DOB
			else FullName
		end desc
	End
GO

Thanks a lot for any help-
- Joel"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources