CODEWITHSUNDEEP
sql-server-2008
MFB
MFB

Reputation: 19787

SQL Server 2008 : IF statement not working as expected

I have a procedure which maps values from a table to a comma-joined string for a report.

Unfortunately, due to an user-interface bug, some of the values are 'none' as opposed to null. I'm pretty sure the 'none' value is just a string, so my procedure tries to handle it as such.

The procedure should return nothing if it encounters 'none', but this isn't the case. For example, if I pass 'none' to @DrugUse below, it should return nothing, right? It still returns 'd', though. So what am I doing wrong?

ALTER FUNCTION [dbo].[MapConsumerAdvice] 
(
    @AdultThemes as VarChar,
    @DrugUse as VarChar
)
RETURNS VarChar(20)
AS
BEGIN
    DECLARE @Result as VarChar(999)

    SET @Result = ''

    IF (@AdultThemes > 0) 
       SET @Result = ',' + 'a'

    IF (@DrugUse IS NOT NULL) AND (@DrugUse != 'none') 
        SET @Result = @Result + ',' + 'd'

    RETURN SUBSTRING(@Result, 2, LEN(@Result))

Upvotes: 1

Views: 327

Answers (2)

Louie Bao
Louie Bao

Reputation: 1732

Books Online:

varchar [ ( n | max ) ]

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Try:

-- Remark 1:
declare @a varchar = 'abcd'
select @a, len(@a)


-- Remark 2:
select cast(1.2345678901234567890123456789 as varchar)

Upvotes: 0

Taryn
Taryn

Reputation: 247660

You are missing a length on the varchar for the parameters:

create FUNCTION [dbo].[MapConsumerAdvice_version2] 
(
    @AdultThemes as VarChar(10),
    @DrugUse as VarChar(10)
)

If you do not put a length, then the value will have a length of one which does not equal none so it will return d. See SQL Fiddle with Demo of two different versions of the function.

Upvotes: 2

Related Questions