将nvarchar值'2,3'转换为数据类型int .....时转换失败？ [英] Conversion failed when converting the nvarchar value '2,3' to data type int.....?

问题描述

嗨



Int = 1，N varchar = 2,3

结合这两个后结果为1,2， 3

我怎样才能在条件中使用concate结果.....？



这是我的汇总函数

（ SELECT  CONCAT（Cities.FK_PrimaryLanguage_ID，' < span class =code-string>，'，Cities.FK_SecondaryLanguages_ID）
  FROM 城市 WHERE  ID =  1 ）

结果为1 ，2,3

这是我的存储过程

  ALTER   proc  [dbo]。[Sp_GetMoviesWith_ReviewList] 
（
 
  @ city  < span class =code-keyword> int  
）
  as  
 开始 
 选择 m.ID  AS  id，m.ReleaseDate  AS  rdate，m.MovieName  AS  title 
 
   -  ，（SELECT avg（Reviews.Rating）FROM评论WHERE Reviews.FK_Movie_ID = m.ID）评级 
，dbo.Movierating（m .ID） as  rating 
   -   ， re.Rating  
，L。语言  AS   LANGUAGE  
，m.status  AS  isactive 
 
，m.MobileThumbnailImage  AS  thumbnail 
，（ SELECT   CASE   WHEN  C. OUNT（Trailers.ID）> 0 那么 '  true '  ELSE  '  false'  END   FROM 预告片 WHERE  Trailers.FK_Movies_ID = m.ID） AS  hastrailers 
，（ SELECT   CASE   WHEN  COUNT（评论.FK_Movie_ID）> 0 那么 '  true'  ELSE  '  false'  END   FROM 评论 WHERE  Reviews.FK_Movie_ID = m.ID） AS  hasreviews 
，m.Description  AS  description 
，m.cast  AS 投放
 
  FROM 电影 as  m 
  INNER   JOIN 语言L  ON  m.FK_Languages_ID = L.ID 
   -    INNER JOIN评论重新开启re.ID = m .ID  
  INNER   JOIN 剧院 ON  Theatres.ID = m.ID 
 
  WHERE  m.status =  1   AND  
 
 m.FK_Languages_ID  IN  
 （ SELECT  CONCAT（Cit ies.FK_PrimaryLanguage_ID，' ，'，Cities.FK_SecondaryLanguages_ID）
   -    FROM CITY WHERE ID = @city） 

Out put：

将nvarchar值'2,3'转换为数据类型int时转换失败。

解决方案

关注此链接：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]

Hi

The Int=1 ,N varchar=2,3
After concate this two the result is 1,2,3
How can i use the concate result in where condition.....?

This is my concate function

( SELECT CONCAT(Cities.FK_PrimaryLanguage_ID,',',Cities.FK_SecondaryLanguages_ID)
           FROM Cities  WHERE ID= 1)

the Results is 1,2,3
This is my stored procedure

ALTER proc [dbo].[Sp_GetMoviesWith_ReviewList]
(

@city int
)
as
begin 
select  m.ID AS id ,m.ReleaseDate AS rdate ,m.MovieName AS title
          
		    --,(  SELECT avg(Reviews.Rating)  FROM Reviews WHERE Reviews.FK_Movie_ID = m.ID) as rating
		    ,dbo.Movierating(m.ID) as rating 
			 --,re.Rating
            ,L.Language AS LANGUAGE
			,m.status AS isactive
			
			,m.MobileThumbnailImage AS thumbnail
			,( SELECT CASE WHEN COUNT(Trailers.ID)>0 THEN 'true' ELSE 'false' END FROM Trailers WHERE Trailers.FK_Movies_ID = m.ID) AS hastrailers
			,( SELECT CASE WHEN COUNT(Reviews.FK_Movie_ID)>0 THEN 'true' ELSE 'false' END FROM Reviews WHERE Reviews.FK_Movie_ID =  m.ID) AS hasreviews
			,m.Description AS description
			,m.cast AS casting
						
		  FROM Movies as m
		  INNER JOIN Languages L ON m.FK_Languages_ID = L.ID
	      --INNER JOIN Reviews re ON re.ID = m.ID
		  INNER JOIN Theatres ON Theatres.ID = m.ID	
		 
		  WHERE m.status = 1 AND
		    
		  m.FK_Languages_ID IN
		      ( SELECT CONCAT(Cities.FK_PrimaryLanguage_ID,',',Cities.FK_SecondaryLanguages_ID) 
			--FROM Cities  WHERE ID= @city)

Out put:

Conversion failed when converting the nvarchar value '2,3' to data type int.

解决方案

Follow this link: Using comma separated value parameter strings in SQL IN clauses[^]

这篇关于将nvarchar值'2,3'转换为数据类型int .....时转换失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！