data type converting issue

Question

0.00/5 (No votes)

See more:

select this my first query
@advisor_id=30;

SQL

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

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

Posted 23-Jan-14 19:28pm

King Fisher

Add a Solution

2 solutions

Solution 1

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

SQL

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

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

exec @query

Posted 23-Jan-14 19:39pm

ArunRajendra

Comments

King Fisher 24-Jan-14 1:43am

advisor_id is integer .so ,we cant pass this as string

ArunRajendra 24-Jan-14 1:47am

I guess you are reading thin from XML and append as varchar? If so this should work just give a try. If you get error post the error will rectify it.

King Fisher 24-Jan-14 2:09am

Error converting data type varchar to bigint.

this is i amgetting while execute it

ArunRajendra 24-Jan-14 2:56am

Can you post the full query?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sujee1 · Accepted Answer · 2014-01-23T20:40:00

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 :

SQL

(
@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.....