Looping in a stored proceedure

What I would like to be able to do but am not sure if I can is the following
.
I need to set a variable = to the results of a single field recordset:
@.X = select EmployeeID from Employees where DepartmentID = 1
Then I need to build a dynamic sql statement based on the above results.
If the first recordset has three records. I need to loop through it three
times and concatinate the results of the sql that would look like this:
This or something like it would be in the loop...
@.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
END RESULT: @.SQL would now look like this
select * from customers where EmpID = 1, select * from customers where EmpID
= 2, select * from customers where EmpID = 3
The text between the comma's can increase or decrease depending on the
number of records in the first sql statement at the top of the page.
Any thoughts
Thank you
KentHi Kent.
You could try:
select ' select * from customers where EmpID=' + EmployeeID from
Employees where DepartmentID = 1
Bryce|||Why not just:
SELECT *
FROM Customers
WHERE empid IN
(SELECT employeeid
FROM Employees
WHERE departmentid = 1)
Dynamic SQL is bad news in a production application. Also, avoid SELECT
*. Code is safer, easier to maintain and maybe more efficient if you
list just the required column names.
David Portas
SQL Server MVP
--|||Kent,
What is the reason of doing this?
why not:
select employeeid, departmentid, ...
from employees
where departmentid = 1
AMB
"Kent Prokopy" wrote:

> What I would like to be able to do but am not sure if I can is the followi
ng.
> I need to set a variable = to the results of a single field recordset:
> @.X = select EmployeeID from Employees where DepartmentID = 1
> Then I need to build a dynamic sql statement based on the above results.
> If the first recordset has three records. I need to loop through it three
> times and concatinate the results of the sql that would look like this:
> This or something like it would be in the loop...
> @.SQL = @.SQL + 'select * from customers where EmpID = ' + @.EmpID + ','
>
> END RESULT: @.SQL would now look like this
> select * from customers where EmpID = 1, select * from customers where Emp
ID
> = 2, select * from customers where EmpID = 3
> The text between the comma's can increase or decrease depending on the
> number of records in the first sql statement at the top of the page.
> Any thoughts
> Thank you
> Kent|||I need to build an Excel report that has each Department on a diferant sheet
.
On each sheet will be a column for each employee. The number of column will
vary depending on the number of employee's in each department. So if
Department Dep1 has 5 employee's the sheet will have five columns. I could
populate each column one at a time, but would like to be able to do this
dynamicly. If posable.
"bd" wrote:

> Hi Kent.
> You could try:
> select ' select * from customers where EmpID=' + EmployeeID from
> Employees where DepartmentID = 1
> Bryce
>|||Correction,
select c.*
from customers as c inner join employees as e
on c.empid = e.employeeid and e.departmentid = 1
AMB
"Alejandro Mesa" wrote:
> Kent,
> What is the reason of doing this?
> why not:
> select employeeid, departmentid, ...
> from employees
> where departmentid = 1
>
> AMB
>
> "Kent Prokopy" wrote:
>|||My bad. Sorry I do not need * from... I need
For each employee I need a column/field.
select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
This will give me three columns in an Excel report. or two columns or XXXXX
"David Portas" wrote:

> Why not just:
> SELECT *
> FROM Customers
> WHERE empid IN
> (SELECT employeeid
> FROM Employees
> WHERE departmentid = 1)
> Dynamic SQL is bad news in a production application. Also, avoid SELECT
> *. Code is safer, easier to maintain and maybe more efficient if you
> list just the required column names.
> --
> David Portas
> SQL Server MVP
> --
>|||select EmpID , count(*) from DataTable
where DataDate = yesterday
GROUP BY EmpID
and do the pivoting in Excel.
Jacco Schalkwijk
SQL Server MVP
"Kent Prokopy" <KentProkopy@.discussions.microsoft.com> wrote in message
news:2126E40D-33DA-4389-A865-73E33F716A0D@.microsoft.com...
> My bad. Sorry I do not need * from... I need
> For each employee I need a column/field.
> select count(*) from DataTable where EmpID = 1 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 2 and DataDate = yesterday,
> select count(*) from DataTable where EmpID = 3 and DataDate = yesterday
> This will give me three columns in an Excel report. or two columns or
> XXXXX
> "David Portas" wrote:
>|||Thank you all for your help/thoughts.
I have come up with a solution that will work.
I am going to build the sql statement in vb code and pass it tp the SP as a
varchar.
"David Portas" wrote:

> Use an Excel Pivot Table for that. You can query the database directly
> and it will create the columns for you. Alternatively you could use
> DTS.
> --
> David Portas
> SQL Server MVP
> --
>|||> I have come up with a solution that will work.
> I am going to build the sql statement in vb code and pass it tp the SP as
a
> varchar.
Ugh, WHY? Sure, that will *work* but it is far and away from the best
solution. This is like going to the grocery store with a list of bar codes
for the products you want to buy.