Stored procedures - 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 procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-24 : 18:08:32

Phil writes "I want to select all the departments from a table then for each department select all the people that work in the department and return this information. I have set up a select to get the department then i select the employees from where the department = there department

CREATE PROCEDURE CN_Select_NamesByDepts

--used for the banked hours system
(
@Dept nchar(20)
)

AS

SELECT DISTINCT @Dept = UserList.Department FROM UserList

--------------------------------
WANT TO RETURN FROM BELOW QUERY!
--------------------------------

SELECT name ,userid FROM UserList

WHERE Department = @Dept

i realise theres some code missing what"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-24 : 19:23:19

Confused, I don't think you need an sp

This returns your employees grouped
select department, employee
from employees
order by department, employee

and this is if you want just one department

select department, employee
from employees
where department = 'SQL coders'
order by department, employee

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

Nazim
A custom title

1408 Posts

Posted - 2002-03-24 : 23:58:09

Why not a sp rrb??? .

CREATE PROCEDURE CN_Select_NamesByDepts

(
@Dept nchar(20)
)

AS
set nocount on
select e.* from dept d
inner join employee e
on e.deptno=d.deptno and d.deptname=@dept
Go

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

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 00:00:29

quote:

Why not a sp rrb??? .

I have no problem with an sp - but doesn't sound like Phil needs one here -

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

Nazim
A custom title

1408 Posts

Posted - 2002-03-25 : 02:39:08

i beg to differ on that , coz phil needs to pass a parameter to select the employees of that department ,a perfect case of using a sp.

quote:

I have no problem with an sp - but doesn't sound like Phil needs one here

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

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 17:10:57

quote:

i beg to differ on that , coz phil needs to pass a parameter to select the employees of that department ,a perfect case of using a sp.

Ah Nazim, no need to beg - you're more than welcome to differ!

Cheers

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources