sql server 存储过程中的 CASE 语句有问题 [英] Trouble with a CASE statement in a sql server stored procedure
问题描述
我刚刚开始在 sql server 中使用存储过程,并且使用得还算不错,但我现在遇到了一个我无法理解的问题.我一直在寻找答案,但老实说,我不确定我问的是对的问题.
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.
我的 sp 是:
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
对于使用任何或所有参数但不带 CASE 但使用 CASE 语句的所有 EXEC,一切正常,以下工作:
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
但是当我尝试时:
EXEC dbo.cafgFindName @NormalizedName = '%Thomas%'
我收到错误:将 nvarchar 值Thomas"转换为数据类型 int 时转换失败.
我无法理解的是转换发生在哪里,如果这确实是问题所在.
What I can't understand is where is the conversion taking place, if indeed that is the problem.
任何建议表示赞赏.
推荐答案
这是因为 CASE 语句必须解析为单一类型.在这种情况下(没有双关语:)你正在考虑一个 nvarchar 或一个 int(我认为 [No] 是一个 int).
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
这篇关于sql server 存储过程中的 CASE 语句有问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!