数据类型转换问题 [英] data type converting issue

问题描述

选择这个我的第一个查询

@ advisor_id = 30;

select this my first query
@advisor_id=30;

SELECT STUFF((SELECT ',' + cast(advisor_id as varchar) AS [text()] FROM tbl_advisor_registration where create_by_user_id=@advisor_id FOR XML PATH('') ), 1, 1, '' )  as advisor_id

结果是



advisor_id

2,3,4,5,6,7,8



i waana得到这个的顾问_详情（2,3,4,5,6,7,8）



@advisor_id = 2,3,4,5,6,7,8; / /这里有哪种数据类型？



从tbl_advior中选择*其中顾问ID在（@advisor_id）



如果我使用int ..am收敛错误



i不能用作varchar，那么这里必须使用哪种数据类型？





谢谢

Result is

advisor_id
2,3,4,5,6,7,8

i waana get the advisor_details of this (2,3,4,5,6,7,8)

@advisor_id =2,3,4,5,6,7,8;//here which datatype have to use?

select *from tbl_advior where advisor id in (@advisor_id)

if i use int ..am getting convering error

i cant be use as varchar, so which datatype have to use here?


Thanks

推荐答案

通过附加@advisor_id使其成为动态查询，如图所示。根据需要设置varchar大小。

Make it a dynamic query by appending the @advisor_id as shown. Set the varchar size as per your need.

declare @query varchar(1000)
declare @advisor_id varchar(1000)

set @query= 'select *from tbl_advior where advisor id in ('+@advisor_id +')'

exec @query

创建一个函数，在数据库的查询窗口中执行它（不要改变任何东西）：

Make a function, execute it in your Database's query window (Don't change anything) :

CREATE FUNCTION [dbo].[Split]
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
		
	Id int identity(1,1),
	Value nvarchar(100)
) 
AS  
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
			Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	Insert Into @RtnValue (Value)
	Select Value = ltrim(rtrim(@List))
	Return
END

然后将您的查询写成：

Then write your query as :

(
@Ad_ID nvarchar(100)
)
as
select * from tbl_advior where advisor_id IN (select Value from dbo.Split(@Ad_ID,','))

如果有帮助，请标记为答案.....

Please mark as answer if it helpful.....

这篇关于数据类型转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！