CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
Peter C
Peter C

Reputation: 553

Trouble with a CASE statement in a sql server stored procedure

I have just started using stored procedures in sql server and have been getting on reasonably well, but I have now come across a problem I can't understand. I have searched for an answer, but to be honest I am not sure I am asking the right question.

My sp is:

ALTER PROCEDURE [FindName]
    @Square nvarchar(100) = null,
    @Location nvarchar(100) = null,
    @Name nvarchar(100) = null,
    @NormalizedName nvarchar(100) = null,
    @SharedLand int = 0,
    @FieldNumber int = 0,
    @Description nvarchar(255) = null,
    @Dwelling nvarchar(100) = null
AS

BEGIN
    SELECT * FROM [Holdings]
    WHERE 
        ([Square] LIKE @Square OR @Square IS NULL)
        AND ([Location] = @Location OR @Location IS NULL)
        AND ([Name] LIKE @Name OR @Name IS NULL)
        AND ([NormalizedName] LIKE @NormalizedName OR @NormalizedName IS NULL)
        AND ([SharedLand] = @SharedLand OR @SharedLand = 0)
        AND ([FieldNumber] = @FieldNumber OR @FieldNumber = 0)
        AND ([Description] LIKE @Description OR @Description IS NULL)
        AND ([Dwelling] LIKE @Dwelling OR @Dwelling IS NULL)
        ORDER BY
            CASE WHEN (@NormalizedName IS NOT NULL) THEN [NormalizedName]
            ELSE [No]
        END
END

It all works fine with all EXECs using any or all parameters without the CASE but with a CASE statement, the following work:

EXEC FindName @Square = '%D%'
EXEC FindName @SharedLand = 1

But when I try:

EXEC dbo.cafgFindName @NormalizedName = '%Thomas%'

I get the error: Conversion failed when converting the nvarchar value 'Thomas' to data type int.

What I can't understand is where is the conversion taking place, if indeed that is the problem.

Any suggestions appreciated.

Upvotes: 2

Views: 1719

Answers (1)

Stephen Byrne
Stephen Byrne

Reputation: 7485

This is because a CASE Statement must resolve to a single type. In this case (no pun intended :) you are considering an nvarchar OR an int (I presume [No] is an int).

See here for more details: Order by and Different Types in a CASE

Upvotes: 5

Related Questions