I am trying to pass a string to the stored procedure by doing this:
EXEC multipleIndexA @inventory_item_id=' 4935,11105, 4935'
I am not getting any results.
Now here is the code of my stored procedure:
CREATE PROCEDURe multipleIndexA
(@inventory_item_id varchar(500))
as
create table #multiple_index
(
topic_title varchar(100),
description_title varchar(100),
start varchar(100),
volume int,
start_time int
)
insert into #multiple_index
(topic_title, description_title, start, volume, start_time)
Select distinct(ltrim(rtrim(tl.topic_title))), rtrim(ltrim(dl.description_title)),
l.volume_number +'/'+ rtrim(ltrim(str(dsl.segment_start_time))) , p.volume_number_int, dsl.segment_start_time
from pbs_vdb_topic_lookup tl, pbs_vdb_description_lookup dl,
pbs_vdb_program p, pbs_vdb_desc_segment_link dsl, pbs_vdb_program_subject_link psl,
pbs_vdb_subject_topic_link stl,pbs_vdb_topic_desc_link tdl, library_index l
where psl.inventory_item_id = p.inventory_item_id
and stl.program_subject_rec_id = psl.program_subject_rec_id
and tdl.subject_topic_rec_id = stl.subject_topic_rec_id
and stl.topic_id = tl.topic_id
and tdl.description_id = dl.description_id
and dsl.topic_description_rec_id = tdl.topic_description_rec_id
and p.inventory_item_id = l.inventory_item_id
and cast (p.INVENTORY_ITEM_ID as varchar(500)) in( @inventory_item_id)
select topic_title, description_title, start
from #multiple_index
order by topic_title, description_title, volume, start_time
GO
p.INVENTORY_ITEM_ID is an int. but I have to find it in a string which is my variable @inventory_item_id with datatype varchar(500).
any ideas how I can get this to work?
Is there a way I can break the string into individual inventory_item_id's? Or what is the best solution to this problem?
EXEC multipleIndexA @inventory_item_id=' 4935,11105, 4935'
I am not getting any results.
Now here is the code of my stored procedure:
CREATE PROCEDURe multipleIndexA
(@inventory_item_id varchar(500))
as
create table #multiple_index
(
topic_title varchar(100),
description_title varchar(100),
start varchar(100),
volume int,
start_time int
)
insert into #multiple_index
(topic_title, description_title, start, volume, start_time)
Select distinct(ltrim(rtrim(tl.topic_title))), rtrim(ltrim(dl.description_title)),
l.volume_number +'/'+ rtrim(ltrim(str(dsl.segment_start_time))) , p.volume_number_int, dsl.segment_start_time
from pbs_vdb_topic_lookup tl, pbs_vdb_description_lookup dl,
pbs_vdb_program p, pbs_vdb_desc_segment_link dsl, pbs_vdb_program_subject_link psl,
pbs_vdb_subject_topic_link stl,pbs_vdb_topic_desc_link tdl, library_index l
where psl.inventory_item_id = p.inventory_item_id
and stl.program_subject_rec_id = psl.program_subject_rec_id
and tdl.subject_topic_rec_id = stl.subject_topic_rec_id
and stl.topic_id = tl.topic_id
and tdl.description_id = dl.description_id
and dsl.topic_description_rec_id = tdl.topic_description_rec_id
and p.inventory_item_id = l.inventory_item_id
and cast (p.INVENTORY_ITEM_ID as varchar(500)) in( @inventory_item_id)
select topic_title, description_title, start
from #multiple_index
order by topic_title, description_title, volume, start_time
GO
p.INVENTORY_ITEM_ID is an int. but I have to find it in a string which is my variable @inventory_item_id with datatype varchar(500).
any ideas how I can get this to work?
Is there a way I can break the string into individual inventory_item_id's? Or what is the best solution to this problem?