Stored Proc fails when item uncommented

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 2005 Forums

Transact-SQL (2005)

Stored Proc fails when item uncommented

Author

Topic

delpi767
Starting Member

11 Posts

Posted - 2009-03-13 : 10:00:56

The following stored procedure works fine with SQL Server 2005

ALTER PROCEDURE Z_PayFromTo

@begdate smalldatetime,
@enddate smalldatetime,
@paidfrom varchar (16),
@cat varchar (16),
@sortorder char (1)

AS
BEGIN
select transactions.date, payee, paidfrom, category, splits.amount
from transactions, splits
where splits.code = transactions.uniq and
category like @cat+'%' and
paidfrom like @paidfrom+'%' and
date between @begdate and @enddate

order by
case
--when @sortorder = 'D' then transactions.date
when @sortorder = 'P' then payee
when @sortorder = 'F' then paidfrom
when @sortorder = 'C' then category
--when @sortorder = 'A' then splits.amount
end
END

However, when I uncomment either of the commented lines in the sort order clause, I receive the following message:

Msg 295, Level 16, State 3, Procedure Z_PayFromTo, Line 11
Conversion failed when converting character string to smalldatetime data type.

Any suggestions would be appreciated.

Regards,

-dmd-

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-03-13 : 10:17:31

each case value should be of same datatype

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-13 : 10:26:54

As Rohitkumar indicated, each case value has to be of the same (or compatible) datatype; work around it by changing the order clause to:

order by
case when @sortorder = 'D' then transactions.date end,
case when @sortorder = 'P' then payee end,
case when @sortorder = 'F' then paidfrom end,
case when @sortorder = 'C' then category end,
case when @sortorder = 'A' then splits.amount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources