conversion error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

conversion error

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2013-04-15 : 21:51:23

Hi,

I am running report having more than 5 parameter, I have used stored procedure which take all parameter as one string only. In stored procedure I am separating these parameter and inserting into temp table. My first parameter is ID and stored in temp table as 1,2,3
When I am assigning this value to my query parameter but I am getting error as

Conversion failed when converting the varchar value '1,2,3' to data type int

Please let me know how to resolve this.

T.I.A

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-15 : 23:32:05

Clearly, you are not seperating them like you think. Show the code

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2013-04-16 : 00:18:43

Thanks

declare @array varchar(8000)
declare @separator char(1)
declare @separator_position int
declare @para1 int,@para2 int, @para3 int, @para4 int, @para5 int

declare @ID varchar(4000),@Name varchar(4000),@PID varchar(4000)
,@Rating varchar(4000),@ObjectiveID varchar(4000)

set @para1 = 1
set @para2 = 1
set @para3 = 1
set @para4 = 1
set @para5 = 1

create table #para_tbl (para varchar(8000))

set @array = '1,2,3~''test'',''Program 1'',''Program 2'',''Program 3'',''Program 4''~null~1,2,3,4~11,12,13,14'
set @array = @array + '~'
while patindex('%~%' , @array) <> 0
begin

select @separator_position = patindex('%~%' , @array)

insert into #para_tbl
select substring(@array,1,@separator_position-1)

select @array = stuff(@array, 1, @separator_position, '')

end

select * from #para_tbl
--set @ID = (select top 1 replace(para,'''''','''') from #para_tbl)
drop table #para_tbl

select *
from Testtbl
where ID in (@ID)
--or Name in (@Name)
--or PID in (@PID)
--or Rating in (@Rating)
--or ObjectiveID in (@ObjectiveID)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-16 : 00:27:18

--Final SELECT statement sholud be
select *
from Testtbl
where ','+@ID+',' LIKE + '%,'+CAST(ID AS VARCHAR(10)) +',%'

--
Chandu

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2013-04-16 : 00:35:02

Thanks :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-16 : 00:44:13

quote:
Originally posted by under2811

Thanks :)

Welcome

The above method will work with the CSV format a,b,c and samll set of values...

If you have large set of values to check, then use Split UDFs..

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources