CODEWITHSUNDEEP
sqlsql-server
user2171512
user2171512

Reputation: 561

Put integer into quotes in MS SQL

i have this variable

DECLARE @MultipleTicketTypeId nvarchar(200) = (SELECT Id FROM #Table1 WHERE Name='Multiple')

and i want to compare that value with @TicketTypeId in

CASE 
    WHEN SUM(FSBT.[RealMoneyBetAmount]) = 0 THEN 0                                        
    WHEN @TicketTypeId = @MultipleTicketTypeId THEN SUM(ISNULL(T1.[DecimalOdds],0) * T1.[RealMoneyBetAmount])  /  SUM(T1.[RealMoneyBetAmount])
    ELSE ''-1''

but i get error

Error converting data type nvarchar to int

Can someone help me ?

EDIT: One more thing that i forgot it: @TicketTypeId will be always in '2' format , so with quotes and @MultipleTicketTypeId is without for now, but should be with ''

Upvotes: 0

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

First, fix the CASE so all return values are numbers.

Second, do the comparison as a string, not a number:

(CASE WHEN SUM(FSBT.[RealMoneyBetAmount]) = 0
      THEN 0                                         
      WHEN CAST(@TicketTypeId, nvarchar(200)) = @MultipleTicketTypeId
      THEN SUM(COALESCE(T1.[DecimalOdds],0) * T1.[RealMoneyBetAmount]) / SUM(T1.[RealMoneyBetAmount])
      ELSE -1
 END)

When given values of different types, SQL Server has to figure out how to do the comparison. For a string and a number, it converts the string to a number. The safest thing is just to do the comparison as a string.

Upvotes: 1

Related Questions