sql - 将nvarchar值'Landmark Acq vPay'转换为数据类型int时转换失败

sql - 将nvarchar值'Landmark Acq vPay'转换为数据类型int时转换失败,第1张

我写了一个SP

Here Column Search is my Column name and it search depending upon column name passed.
Example :
usp_Pricing_Paged_Get_Test @ColumnSearch = 'PricingId', @SearchTerm = 1
It gives correct result

But when i execuete
usp_Pricing_Paged_Get_Test @ColumnSearch = 'PricingName', @SearchTerm = 'M'
It gives error "Conversion failed when converting the nvarchar value 'Landmark Acq vPay ' to data type int.

CREATE PROCEDURE [dbo].[usp_Pricing_Paged_Get_Test] @pStartIndex INT = 0
    ,@pPageSize INT = 10
    ,@pOrderBy VARCHAR(50) = 'PRICINGID DESC'
    ,@ColumnSearch VARCHAR(50)
    ,@SearchTerm VARCHAR(50)
AS
BEGIN
    --Paged Pricing
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER (
                ORDER BY CASE 
                        WHEN @pOrderBy = 'PRICINGID ASC'
                            THEN X.PricingId
                        END ASC
                    ,CASE 
                        WHEN @pOrderBy = 'PRICINGID DESC'
                            THEN X.PricingId
                        END DESC
                    ,CASE 
                        WHEN @pOrderBy = 'PRICINGNAME ASC'
                            THEN X.PricingName
                        END ASC
                    ,CASE 
                        WHEN @pOrderBy = 'PRICINGNAME DESC'
                            THEN X.PricingName
                        END DESC
                    ,CASE 
                        WHEN @pOrderBy = 'STATENAME ASC'
                            THEN X.StateName
                        END ASC
                    ,CASE 
                        WHEN @pOrderBy = 'STATENAME DESC'
                            THEN X.StateName
                        END DESC
                ) AS ROW
            ,*
        FROM (
            SELECT T.PRC_ID AS PricingId
                ,PRC.PRC_SCENE_NM AS PricingName
                ,WKFLOW_STATE.WKFLOW_STATE_CD AS StateCode
                ,WKFLOW_STATE.WKFLOW_STATE_NM AS StateName
            FROM WORFLOW_EVENT_LOG
            INNER JOIN (
                SELECT MAX(WKFLOW_ACT.WKFLOW_ACT_ID) WKFLOW_ACT_ID
                    ,WKFLOW_ACT.PRC_ID
                FROM WKFLOW_ACT
                INNER JOIN WORFLOW_EVENT_LOG ON WKFLOW_ACT.WKFLOW_ACT_ID = WORFLOW_EVENT_LOG.WKFLOW_ACT_ID
                GROUP BY WKFLOW_ACT.PRC_ID
                ) T ON WORFLOW_EVENT_LOG.WKFLOW_ACT_ID = T.WKFLOW_ACT_ID
            INNER JOIN PRC ON T.PRC_ID = PRC.PRC_ID
            INNER JOIN WKFLOW_STATE_TRNST ON WORFLOW_EVENT_LOG.WKFLOW_STATE_TRNST_ID = WKFLOW_STATE_TRNST.WKFLOW_STATE_TRNST_ID
            INNER JOIN WKFLOW_STATE ON WKFLOW_STATE_TRNST.TO_STATE_CD = WKFLOW_STATE.WKFLOW_STATE_CD
            ) AS X
        ) AS PrcingWithRowNumber
    WHERE StateCode = 'APP'
        AND Row >= @pStartIndex   1
        AND Row <= @pStartIndex   @pPageSize
        AND  (
            CASE 
                WHEN @ColumnSearch LIKE '%PricingId%'
                    THEN PricingId
                WHEN @ColumnSearch LIKE '%PricingName%'
                    THEN PricingName
                END  like '%'   @SearchTerm   '%'
            )

    ORDER BY Row
END

最佳答案:

2 个答案:

答案 0 :(得分:1)

我想改变上一个case statement应该可以解决您的问题。

  

Case语句从集合中返回最高优先级类型   result_expressions中的类型和可选的else_result_expression。

所以input_expression和每个when_expression的数据类型必须相同或必须是隐式转化

  CASE
     WHEN @ColumnSearch LIKE '%PricingId%' THEN Cast(PricingId AS VARCHAR(50))
     WHEN @ColumnSearch LIKE '%PricingName%' THEN PricingName
   END   like '%'   @SearchTerm   '%'

答案 1 :(得分:1)

问题是这段代码:

    AND  (
        CASE 
            WHEN @ColumnSearch LIKE '%PricingId%'
                THEN PricingId
            WHEN @ColumnSearch LIKE '%PricingName%'
                THEN PricingName
            END  like '%'   @SearchTerm   '%'
        )

我会通过删除case

来解决此问题
((@ColumnSearch LIKE '%PricingId%' and
  cast(PricingId as varchar(255)) like '%'   @SearchTerm   '%') or
 (@ColumnSearch LIKE '%PricingName%' and
  PricingName like '%'   @SearchTerm   '%')
)

窗口语句中的order by子句表示您了解类型和case表达式的问题。 case只返回一种类型,这可能会导致转换不当。

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复