CODEWITHSUNDEEP
sqlsql-server
StevieB
StevieB

Reputation: 6541

Conditional order by case statement error : String or binary data would be truncated

I have a table valued function where I am trying to add a conditional case order by statement with a bit value. This is in Sql Server 2012. I keep running into the same error trying to add the case statement.

ALTER function [dbo].[myFunction] 
(
     @user varchar(30)
    ,@double_entry_flag bit
)
...
begin 
ORDER BY CASE WHEN @double_entry_flag = 1 
THEN total_unique_groupcount WHEN @double_entry_flag = 0 
THEN unique_groupcount END DESC
end;

keep getting error

Msg 8152, Level 16, State 13, Line 4
String or binary data would be truncated.
The statement has been terminated.

But when I switch my order by statement back to normal again i.e.

ORDER BY total_unique_groupcount DESC

It works fine, there's something about the Syntax or the Case statement it doesn't like. Any Ideas ?

Upvotes: 0

Views: 379

Answers (1)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

Try like this

ORDER BY 
CASE WHEN @double_entry_flag = 1 THEN total_unique_groupcount END DESC,
CASE WHEN @double_entry_flag = 0 THEN unique_groupcount  END DESC

Upvotes: 0

Related Questions