在SQL Server中出错“将varchar值'％'转换为数据类型int时转换失败。 [英] Error In sql Server " Conversion failed when converting the varchar value '%' to data type int."

问题描述

请立即帮助我 ......

  ALTER   PROCEDURE  [dbo]。[csp_Objekt_GetAllObjekts] 
（
  @ StreetNo   int ，  -   搜索选项 
  @ FirstRow   Int ，  -  -   起始行号 
  @ LastRow   Int ，  -   结束行号 
  @ SortColumn   varchar （ 30 ），  -   排序列名 
  @ SortOrder  < span class =code-keyword> varchar （ 4 ）  -   排序方向（ASC / DESC） 
）
 
  AS  
  SET   NOCOUNT   ON  
 < span class =code-keyword> DECLARE   @ OrderBy   varchar （ 40 ）
 
   -   设置排序列和排序零值为空值 
  IF   @ SortColumn   IS   NULL  
  SET  @ SortColumn = ISNULL（ @ SortColumn ，'  '）
  IF   @ SortOrder   IS   NULL  
  SET  @ SortOrder = ISNULL（ @ SortOrder ，' '）
 
   -   组合排序列和排序顺序 
  SET  @ OrderBy = @ SortColumn + @ SortOrder 
 
  SELECT  *  FROM  
（
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  
 
   -   将StreetNo排​​序为升序 
  CASE  
  WHEN   @ OrderBy  = '  StreetNoASC' 那么 StreetNo 
  END   ASC ，
   -   将StreetNo排​​序为降序 
  CASE  
  WHEN   @ OrderBy  = '  StreetNoDESC' 那么 StreetNo 
  END   DESC ，
 
   -   默认按ObjektName排序 
  CASE  
  WHEN   @ OrderBy  = ' ' 那么 ObjektName 
  END   ASC  
） AS  RowNum，
 
 COUNT（*） OVER （） AS  TotalObjekts，
 
 ObjektId，
 ObjektName，
 Street，
 StreetNo，
 Land，
 PLZ，
 Ort，
 CreatedDate ，
 IsContactPerson，
 IsDocument，
 IsPhoto，
注意
  FROM  
 Objekt  WITH （ NOLOCK ）
  WHERE  
   -   过滤StreetNo  
（ @St reetNo   IS   NULL  或  @ StreetNo  =  0  或 StreetNo  LIKE   @ StreetNo  + ' ％'）
 
） AS  ObjektsData 
  WHERE  RowNum> = @ FirstRow  AND  RowNum< = @ LastRow   -   仅采用给定的范围记录 
  SET   NOCOUNT   OFF  

解决方案

你需要使用如下的演员表

（ @ StreetNo   IS   NULL  或  @ StreetNo  =  0  或 StreetNo  LIKE  cast（ @ StreetNo   as   varchar （ 10 ））+ ' ％' ）

检查此项SQL小提琴 [ ^ ]

Please Help me immediately bro....

ALTER PROCEDURE [dbo].[csp_Objekt_GetAllObjekts]      
(                                            
		@StreetNo int,                -- Search option 		
		@FirstRow Int,						      -- Starting Row Number
		@LastRow Int,							  -- Ending Row Number
		@SortColumn varchar(30),				  -- Sort Column Name
		@SortOrder varchar(4)					  -- Sort Direction (ASC/DESC)
)

AS
SET NOCOUNT ON
DECLARE @OrderBy varchar(40)

-- Setting Sort Column and Sort Order null for empty value
IF @SortColumn IS NULL
SET @SortColumn=ISNULL(@SortColumn,'')
IF @SortOrder IS NULL
SET @SortOrder=ISNULL(@SortOrder,'')

-- Combining Sort Column and Sort order
SET @OrderBy=@SortColumn+@SortOrder

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY

-- Sorting StreetNo as Ascending order
CASE
WHEN @OrderBy = 'StreetNoASC' THEN StreetNo
END ASC,
-- Sorting StreetNo as Descending order
CASE
WHEN @OrderBy = 'StreetNoDESC' THEN StreetNo
END DESC,

-- Default Sorting by ObjektName
CASE
WHEN @OrderBy = '' THEN ObjektName
END ASC
) AS RowNum,

COUNT (*) OVER () AS TotalObjekts,

ObjektId,
ObjektName,
Street,
StreetNo,
Land,
PLZ,
Ort,
CreatedDate,
IsContactPerson,
IsDocument,
IsPhoto,
Notice
FROM
Objekt WITH(NOLOCK)
WHERE
-- Filtering StreetNo      
(@StreetNo IS NULL OR @StreetNo = 0 OR StreetNo LIKE @StreetNo + '%')

)AS ObjektsData
WHERE RowNum >=@FirstRow AND RowNum<=@LastRow -- Taking only the given range records
SET NOCOUNT OFF

解决方案

you need to use cast as below

(@StreetNo IS NULL OR @StreetNo = 0 OR StreetNo LIKE cast(@StreetNo as varchar(10)) + '%')

check this SQL Fiddle[^]

这篇关于在SQL Server中出错“将varchar值'％'转换为数据类型int时转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！