Passing a string to a stored procedure

[mashaguen]

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?

 

[Cagliostro]

there are two variants:
EXEC multipleIndexA(' 4935,11105, 4935')
or
EXEC multipleIndexA(@inventory_item_id) John Fill

ivfmd@mail.md

 

[mashaguen]

Two variants?

Actually I get the values for @inventory_item_id from my ASP page.
In the asp page inventory_item_id is being build as a string so:

when I print out inventory_item_id is 10945,6224, 10945

then i execute the stored procedure by :
strQuery = "EXEC multipleIndexA"
strQuery = strQuery & " @inventory_item_id='" & inventory_item_id & "'"
vdb.execute(strQuery)

when I print out the strQuery I get : EXEC multipleIndexA @inventory_item_id=' 10945,6224, 10945'

To double check the code I run it from query analyzer and I get no results.

What is wrong? I dont know.

You already saw a code of my stored procedure.

 

[JNC73]

You only have to pass the value of inventory_item_id to the strQuery and DO NOT add @inventory_item_id= to the string

So the print result of strQuery should be

EXEC multipleIndexA '10945,6224,10945'

Hope this helps

JNC73

 

[mashaguen]

Actually this is exactly what the print results of the strQuery return. -- EXEC multipleIndexA '10945,6224,10945'

 

[tlbroadbent]

If INVENTORY_ITEM_ID is an integer data type you want to search for integer data type, not character. You need to build a dynamic SQL statement and then execute it in the SP. He is the procedure that I recommend. Hopefully, I've typed it correctly. I tried to highlight the changes I made.

--------------------------

CREATE PROCEDURE MultipleIndexA @inventory_item_id varchar(500) As

Declare @sql varchar(1024)

Set nocount on

Create Table #multiple_index (topic_title varchar(100), description_title varchar(100), start varchar(100), volume int,
start_time int)

Select @sql="
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 p.INVENTORY_ITEM_ID in (" + @inventory_item_id + &quot"

sp_executesql @sql /* This will execute the Insert Into statement */

Select topic_title, description_title, start
From #multiple_index

Order By topic_title, description_title, volume, start_time

GO
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot