Need Help on Stored Procedure Variables

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

SQL Server Development (2000)

Need Help on Stored Procedure Variables

Author

Topic

SoftCompTools
Starting Member

2 Posts

Posted - 2007-10-29 : 12:17:59

Hi

I have a stored procedure where i am selecting a table joining many tables and getting back a set of records. I want to store the ids of those records to a variable with comma separated and query other tables based on the string which i saved.

1. I am ready with a the query structure and getting back with a set of ids.
2. I am ready with the string which i am parsing to generate the ids and query the tables one by one to get the result.

I don't know how to use a variable which can store my first query response in a Varchar like "1,7,27,89,45".

Query:

select distinct eav.entity_id from

TEST_Entity_Attribute_Value as eav
join TEST_Entity_Attribute_Class_Mapping as eacm on eacm.id = eav.entity_attribute_class_mapping_id
join TEST_Entity_Attribute_Type as eat on eat.id = eacm.entity_attribute_type_id
join TEST_Entity_Type_Entity_Attribute_Mapping as eteam on eteam.entity_attribute_type_id = eat.id
join TEST_Entity_Type as et on et.id = eteam.entity_type_id
join TEST_Entity_Attribute_Class as eac on eac.id = eacm.entity_attribute_class_id
join TEST_Entity_Attribute_Class_Group_Mapping as eacgm on eacgm.entity_attribute_class_id = eac.id
join TEST_Entity_Attribute_Class_Group as eacg on eacg.id = eacgm.entity_attribute_class_group_id
where eat.status >= 1000
and et.status >= 1000
and eteam.status >= 1000
and eacm.status >= 1000
and eac.status >= 1000
and eacg.status >= 1000
and eat.id = 7
and eav.attribute_value = 'Manufacturing'

Please help me....

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-29 : 12:28:09

It woulld be easier to store tha results in a table variable
declare @t as table (c1 int)
and use an in clause

select * from my_tables where id in (select c1 from @t)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

SoftCompTools
Starting Member

2 Posts

Posted - 2007-10-29 : 13:04:22

Can you tell me a way to store the recordset of
Select id From Emp
query in c1 of @t.

After this i can join this @t to my long query and find the resultset.

Appreciate your help...

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-29 : 16:29:54

[code]
select id into #t from EMP -- add any where condition to restrict the IDs

select m.* from my_table m where m.id in (select #t.id from #t)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources