Stored Procedure question - 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

SQL Server Development (2000)

Stored Procedure question

Author

Topic

pchari
Starting Member

4 Posts

Posted - 2002-03-03 : 19:02:22

I have 4 or 5 tables.
say tab1 with primarykey u1, tab1tab2 (u1,g1),tab2 primaryKey g1, tab2tab3(g1,k1),tab3 primarykey k1,tab1tab3 (u1,k1).
I have a view for (u1,k1).(one to many)
I have a view for all these tables.
I have to create a temp table for all the fields of tab1 and k1 where
k1 is in (1,2,3,4,5) in a stored proc and return the result.
I would appreciate the help.
thanks.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-04 : 18:22:57

How's this?

SELECT *
FROM tab1
JOIN tab1tab3 on tab1.u1 = tab1tab3.u1
JOIN tab3 on tab1tab3.k1 = tab3.k1
WHERE tab3.k1 in (1,2,3,4,5)

If you're returning the result, then I think a temp table is unnecessary, unless there is something else you have to do with it. If that's the case, then just add an INTO #TempTable clause.
------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 03/04/2002 18:24:23

Edited by - AjarnMark on 03/04/2002 18:25:16

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-04 : 18:26:45

perhaps I have misunderstood, but otherwise, I think you want this:

CREATE PROCEDURE dbo.spValues A
select *
into #a
from view1
where k1 in (1,2,3,4,5)

select * from #a
drop table #a

What I don't understand is why you don't just filter your view with the "in" clause -

If you already have the view, view1, then just do this:

select * from view1 where k1 in (1,2,3,4,5)

You don't appear to need a stored procedure at all (or a temp table for that matter)....

Possibly need some more info here...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

pchari
Starting Member

4 Posts

Posted - 2002-03-04 : 18:35:25

found a solution for this issue.
I have created a view for u1,k1.
But this view has one to many relationship.
If u1 has k1=2,k1=3 then,
When i do the query that you suggested above by RRB, I get both of these.
I want the highest.
Here is the solution.
"select * from view1 group by u1 having k1= min(k1)"
works fine.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources