CODEWITHSUNDEEP
sqlsql-serverstored-procedurescase
pancake
pancake

Reputation: 600

CASE statement giving wrong results inside my stored procedure

I have a case inside my stored procedure which I use before executing the data.

DECLARE @Setup nvarchar(50)

SELECT 
   @ZipCode = CASE 
                 WHEN REPLACE(@ZipCode, '0', '') = '' 
                   THEN NULL ELSE @ZipCode 
              END,
   @ZipCodeEND = CASE 
                   WHEN REPLACE(@ZipCodeEND, '0', '') = '' 
                     THEN NULL ELSE @ZipCodeEND 
                 END, 

SELECT
    @Setup = CASE WHEN (LEN(ISNULL(@ZipCode, ''))) > 0 THEN '1' ELSE '0' END +
             CASE WHEN (LEN(ISNULL(@ZipCodeEND,''))) > 0 THEN '1' ELSE '0' END

IF ISNULL(@ID, 0) = 0 
BEGIN
   INSERT INTO dbo.MapToStaticValuesTable(ZipCode, ZipCodeEND, Setup)
   VALUES(@ZipCode, @ZipCodeEND, @Setup)
END

The problem here is even if zipcode and zipcodeEnd are empty and set to null after being saved into the table I keep getting the value "11" instead of getting "00".

Now if I do the same example with nvarchar values it would work, but since ZipCode and ZipCodeEnd are set to int it's acting weird.

Upvotes: 0

Views: 190

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

It is acting weird because you are using string functions on integers. Not sure what you are trying to achieve with your code but I'm sure it can be done just by checking the values as integers.

I guess this could be what you are looking for.

select case when nullif(@ZipCode, 0) is null then '0' else '1' end +
       case when nullif(@ZipCodeEND, 0) is null then '0' else '1' end

One example of weird

select isNull(@ZipCode, '')

return 0 if @ZipCode is null.

Upvotes: 3

Related Questions