我正在尝试创建一个存储的Proc。但它使我感到“将varchar值'Blue color'转换为数据类型int时转换失败”错误。我不知道哪里有问题，请帮助我
ALTER PROCEDURE [dbo].[GetPerDiem]
@KeyWord varchar(10)
AS
BEGIN
set nocount on
IF @KeyWord=1
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Jan,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=2
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Feb,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=3
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Mar,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=4
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Apr,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=5
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,May,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=6
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Jun,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=7
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,JUl,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=8
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Aug,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=9
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Sep,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=10
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Oct,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
Else IF @KeyWord=11
BEGIN
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Nov,Meals as 'Meals & Inc. Exp.**' from PerDiem
END
ELSE IF @KeyWord=12
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Dec,Meals as 'Meals & Inc. Exp.**' from PerDiem
ELSE
Select City as 'Primary Destination',LocationDefined as [(County 2,3)],County,Oct,Nov,Dec ,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Meals as 'Meals & Inc. Exp.**' from PerDiem
where [FiscalYEAR] =2015
and [state] like '%'+@KeyWord+'%'
or [City] like '%'+@KeyWord+'%'
or [Zip] like '%'+@KeyWord+'%'
or [County] like '%'+@KeyWord+'%'
END
正如JamesZ在评论中说的那样，您已将参数'@KeyWord'声明为，VARCHAR(10)但在IF-Else中用整数对它的值进行了补偿，就像这样会使IF @KeyWord=1您出错。
总体而言，您的逻辑也不适用于SQL查询。我认为您希望根据您的参数使用不同的月份，因此存储的proc可能类似于：
ALTER PROCEDURE [dbo].[GetPerDiem]
@KeyWord INT
AS
BEGIN
set nocount on
IF @KeyWord=1
BEGIN
Select City as 'Primary Destination',
LocationDefined as [(County 2,3)],
County,
Month=CASE
WHEN @KeyWord=1 THEN 'Jan'
WHEN @KeyWord=2 THEN 'Feb'
WHEN @KeyWord=3 THEN 'Mar'
WHEN @KeyWord=4 THEN 'Apr'
WHEN @KeyWord=5 THEN 'May'
WHEN @KeyWord=6 THEN 'Jun'
WHEN @KeyWord=7 THEN 'Jul'
WHEN @KeyWord=8 THEN 'Aug'
WHEN @KeyWord=9 THEN 'Sep'
WHEN @KeyWord=10 THEN 'Oct'
WHEN @KeyWord=11 THEN 'Nov'
WHEN @KeyWord=12 THEN 'Dec'
ELSE 'Oct,Nov,Dec ,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep'
END
Meals as 'Meals & Inc. Exp.**'
from PerDiem
where [FiscalYEAR] =2015
and [state] like '%'+@KeyWord+'%'
or [City] like '%'+@KeyWord+'%'
or [Zip] like '%'+@KeyWord+'%'
or [County] like '%'+@KeyWord+'%'
END
或者
最好有一个Varchar参数，@month Varchar(100)并使用if else在select循环外分配其相应的month值，并在select中使用该一次性计算的变量。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句