SQL Server for loop assistance

Question

Ok so this is my stored proc so far.

    ALTER Procedure GetJobInfo()
    AS
    BEGIN
        Select EmployeeId, FirstName, LastName
        From dbo.Employees
        for EmployeeId 
            SELECT ComputerCodeId 
            From dbo.EmployeeJobs 
            Where ComputerCodeId = "F"
    END

I need to get the employees which I have done, but then I need to get the employees jobs from the dbo.EmployeeJobs table that match an array of computer codes. How would I go about doing something like that? My end goal is to be able to create a report that breaks the employee jobs into two categories based on the computer code associated with them and then getting the sum of the two categories and having them populate two different columns.

Plain TSQL is not suited or designed to produce "reports". Writing reports usually involves some sort of application that is intended for this purpose - even if that app is something like Excel. You should approach this task from the perspective of writing a report using the tool you have chosen rather than from generic tsql. — SMor, Aug 06 '18 at 13:17
I'm using entity framework core 2, end goal is to be able to return everything as a list and then populate reportviewer from said list — brock, Aug 08 '18 at 03:38

score 1 · Accepted Answer · answered Aug 06 '18 at 06:03

1

I am not totally sure to understand what you want but you have this two options.

Try to use this option if your table EmployeeJobs contains the column EmployeeId.

SELECT EJ.ComputerCodeId , E.EmployeeId, E.FirstName, E.LastName FROM dbo.EmployeeJobs As EJ INNER JOIN dbo.Employees As E ON EJ.EmployeeId = E.EmployeeId WHERE EJ.ComputerCodeId = 'F'

Otherwise if you can't match the EmployeeId you could use the following, this will filter by the ComputerCodeId and cross by each employee.

SELECT EJ.ComputerCodeId , E.EmployeeId, E.FirstName, E.LastName FROM dbo.EmployeeJobs As EJ, dbo.Employees As E WHERE EJ.ComputerCodeId = 'F'

answered Aug 06 '18 at 06:03

Antonio Rodriguez

282
2
11

Ok so I've got that in there and It all seems to work fine until I try to get the computer code id. Where dbo.EmployeeJobs.ComputerCodeId = "1" is what it should be but it is saying invalid column name "1" for some reason. The ComputerCodeId will be an int. any clue on how to fix that error? – brock Aug 06 '18 at 06:13
Probably is because you are using double quotes instead of singles, but if you column is int just put 1 without quotes. – Antonio Rodriguez Aug 06 '18 at 06:20
how would I go about setting the Where EJ.ComputerCodeId equal to an array of ints? – brock Aug 06 '18 at 06:29
If you want to go through all the values just remove the where clause, if not you can use WHERE EJ.ComputerCodeId in (1,5,6) – Antonio Rodriguez Aug 06 '18 at 06:36

score 0 · Answer 2 · edited Aug 06 '18 at 06:01

0

You can try join instead of for loop

ALTER Procedure GetJobInfo()
AS
BEGIN
    SELECT EmployeeId, FirstName, LastName
    FROM dbo.Employees E
    INNER JOIN dbo.EmployeeJobs EJ
    ON E.EmployeeId = EJ.EmployeeId
    Where EJ.ComputerCodeId = "F"
END

edited Aug 06 '18 at 06:01

Barr J

10,636
1
28
46

answered Aug 06 '18 at 05:46

Om Veer

55
1
9

inner join is not recommended as some of the records will be lost (records containing nulls) and performance can be affected if not indexed well. – Barr J Aug 06 '18 at 05:48
The records can be lost only in case EmployeeId is null. I don't think that any record will be there with null EmployeeId. – Om Veer Aug 06 '18 at 05:55
Yes I would always have an EmployeeId. How would I go about making it so the computerCodeId could be equal to an array of computer codes? – brock Aug 06 '18 at 06:00
you cannot tell, you can only assume, he did not provide any info regarding EmployeeId. furthermore, if EmployeeId is not indexed well it will impact performance. We cannot guess, we need to work with what we have :) – Barr J Aug 06 '18 at 06:00
This program is for a company with about 20 Employees, they will all have an Id of type INT. – brock Aug 06 '18 at 06:04
@brock can you please provide some sample data and expected result? – Om Veer Aug 06 '18 at 06:10
Hey @brock if my answer is helpful to you then please vote my answer... :) – Om Veer Aug 06 '18 at 12:51

score 0 · Answer 3 · answered Aug 06 '18 at 05:47

you need to look at the while loop of sql.

It will provide what you need and is the method to use loops in queries.

the basic syntax is:

DECLARE @cnt INT = 0;

    WHILE @cnt < cnt_total
    BEGIN
       {...statements...}
       SET @cnt = @cnt + 1;
    END

and this example will simulate for loop:

DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Inside simulated FOR LOOP on TechOnTheNet.com';
   SET @cnt = @cnt + 1;
END;

PRINT 'Done simulated FOR LOOP on TechOnTheNet.com';
GO

you can find more info about WHILE loop in Microsoft Docs.

investigate and read about it, then give it a shot.

edit: for additional method, look at SQL Server FOR EACH Loop post.

SQL Server for loop assistance

3 Answers3