Problem in IN statement - 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 2000 Forums

Transact-SQL (2000)

Problem in IN statement

Author

Topic

ikhuram
Starting Member

28 Posts

Posted - 2004-05-26 : 02:50:30

I have a table with a field tmp_id. It has values
0011
0012
0013 and so on
I store the where clause in another temp table's respective column
as ('0011, 0012, 0013') When I use
select tmp_id, tmp_dsc from Table where tmp_id in (select col1 from tmp_col). It does not return any rows. Please tell me how to fix this problem
CODE:
if exists(select 1 from sysobjects where name = 'sp_test')
drop proc sp_test
go
create proc sp_test
as
begin
set nocount on

insert into col values('0012, 0013, 0014, 0015', 'Program1, Program2, Program3')

create table #tmp_col
(
col1 varchar(50),
col2 varchar(80)
)
insert into #tmp_col select * from col
delete col
select tmp_id, tmp_dsc from temp
where tmp_id in (select col1 from #tmp_col) and
tmp_dsc in (select col2 from #tmp_col)

end
go
sp_test

gates_micro
Starting Member

29 Posts

Posted - 2004-05-26 : 03:43:18

You IN clause is not right. Since you are checking the IN clause in a single string So its not fetching you the values.
WHERE ID IN ('0012,0015,0014,0015').

Use the IN clause as

WHERE ID IN ('0012','0015','0014')

gates_micro
Starting Member

29 Posts

Posted - 2004-05-26 : 09:17:39

Here is the way to do it.

First compile the function and then the procedure.

1) Function which will format the string you need to put in the IN subquery

CREATE FUNCTION FORMATSTRING(@inputstr varchar(30))
RETURNS varchar(1000)
as
BEGIN
declare @offset int
declare @cur int
declare @tmp varchar(4)
declare @len int
declare @i int
declare @inpt varchar(30)
declare @val varchar(1000)

set @i=0
set @inpt = @inputstr
set @len = len(@inpt)
set @cur=0

while(@i<@len)
begin

set @offset = charindex(',',@inpt,@i)

if @offset = 0
begin
set @offset = charindex(',',@inpt,@i-1)
set @cur=@len
end

set @tmp = ltrim(substring(@inpt,@i,@offset))

set @val = '''' + @tmp + '''' + ',' + isnull(@val,'')

if @cur=@len
begin
--print substring(@val,0,len(@val))
break
end
set @i = @offset + 1
end

return substring(@val,0,len(@val))
END

2)

create proc sp_test
as
begin
declare @sel_stmt varchar(1000)
declare @insert_stmt varchar(1000)
declare @create_stmt varchar(1000)
declare @del_stmt varchar(1000)

set @insert_stmt = 'insert into col values(' + '''0012, 0014, 0016, 0020''' + ',' + '''Prg1, Prg2, Prg3''' + ')'
print @insert_stmt
execUTE(@insert_stmt)

set @create_stmt = 'create table tmp_col (col1 varchar(50),col2 varchar(80))'
print @create_stmt
execUTE(@create_stmt)
set @insert_stmt = 'insert into tmp_col SELECT * FROM COL'

print @insert_stmt
execUTE(@insert_stmt)

set @del_stmt = 'delete col'
print @del_stmt
execUTE(@del_stmt)

declare @tmp_id varchar(100)
set @tmp_id =(SELECT COL1 FROM TMP_COL)
declare @tmp_dsc1 varchar(100)
set @tmp_dsc1 =(SELECT COL2 FROM TMP_COL)

declare @tmp_dsc varchar(100)
set @tmp_dsc =(SELECT dbo.FORMATSTRING(@tmp_dsc1))

set @sel_stmt ='select tmp_id, tmp_dsc from temp where tmp_id in (' + '' + @tmp_id + '' + ')' + 'and tmp_dsc in (' + '' + @tmp_dsc + '' + ')'

--+ ' and tmp_dsc in (select col2 from tmp_col)'
print @sel_stmt
execUTE(@sel_stmt)
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources