Array like functionality in SQL Server 2008

Question

I want to read EmpID in EMP Table based on some condition. For every EmpID I need to do some operation in another table. How can I read single value of EmpID at a time.

Thanks in advance

Answer 1

UPDATE otherTable... 
WHERE table2.EmpID IN (SELECT EMP.EmpID FROM EMP WHERE ...)

Answer 2

Generally, you should avoid procedural code in SQL, but if you really need to, use CURSOR:

DECLARE myCursor CURSOR FAST_FORWARD
FOR
    SELECT    --your SQL query, a regular SQL query.
        field1,
        field2
    FROM 
        table

OPEN myCursor;
FETCH NEXT FROM myCursor 
INTO 
    @var1, --must be pre-declared, of the same types as field1
    @var2

WHILE (@@FETCH_STATUS = 0) 
BEGIN


    --your code use @var1, @var2. Perform queries, do whatever you like. 
    --It will loop through every row fetched by the query in the beginning of the code, and perform this.


    FETCH NEXT FROM myCursor --do this exactly as before the WHILE loop
    INTO 
        @var1,
        @var2
END
CLOSE myCursor

Answer 3

Using a set based approach to SQL logic is always the preferred approach. In this sense DanDan's is an acceptable response. Alternatively you could use SQL cursors. Although resource heavy they will allow you iterate through a set and apply some logic on each row.

DECLARE @EMPID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT EmpID
FROM EMP

WHERE *some_clause*

OPEN c1

FETCH NEXT FROM c1
INTO @EMPID

WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT @EMPID

    FETCH NEXT FROM c1
    INTO @EMPID

END

CLOSE c1
DEALLOCATE c1

Answer 4

Following on from DanDan's Answer, T-SQL allows you to do join in the FROM clause of an UPDATE statement (I can't remember if this is ANSI or not). EG

UPDATE 
    OtherTable
SET 
    Auditing = Employees.EmployeeName
FROM 
    OtherTable
    INNER JOIN 
        Employees ON OtherTable.EmpId = Employees.EmpId
WHERE
    Employees.DateStarted > '2010-09-01'

Answer 5

try to never loop, work on sets of data.

you can insert, update, delete multiple rows at one time. here in an example insert of multiple rows:

INSERT INTO YourTable
        (col1, col2, col3, col4)
    SELECT
        cola, colb+Colz, colc, @X
        FROM ....
            LEFT OUTER JOIN ...
        WHERE...

you can even insert into multiple tables in a single statement:

INSERT INTO YourTable
        (col1, col2, col3, col4)
        OUTPUT INSERTED.PK, Inserted.Col2
            INTO OtherTable (ColA, ColB)
    SELECT
        cola, colb+Colz, colc, @X
        FROM ....
            LEFT OUTER JOIN ...
        WHERE...

When looking at a loop see what it done inside it. If it is just inserts/deletes/updates, re-write to use single commands. If there are IFs, see if those can be CASE statements or WHERE conditions on inserts/deletes/updates. If so, remove the loop and use set commands.

I've taken loops and replaced them with the set based commands and reduced the execution time from minutes to a few seconds. I have taken procedures with many nested loops and procedure calls and kept the loops (was impossible to only use inserts/deletes/updates), but I removed the cursor, and have seen less locking/blocking and massive performance boosts as well. Here are two looping methods that are better than cursor loops...

if you have to loop, over a set do something like this:

--this looks up each row for every iteration
DECLARE @msg VARCHAR(250)
DECLARE @hostname sysname

--first select of currsor free loop
SELECT @hostname= min(RTRIM(hostname))
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <> ''

WHILE @hostname is not null
BEGIN
    --just some example of some odd task that requires a loop
    set @msg='exec master.dbo.xp_cmdshell "net send ' 
        + RTRIM(@hostname) + ' '
        + 'testing  "'
    print @msg
    --EXEC (@msg) --<<will not actually send the messages

    --next select of cursor free loop
    SELECT @hostname= min(RTRIM(hostname))
        FROM master.dbo.sysprocesses (NOLOCK)
        WHERE  hostname <> ''
        and hostname > @hostname
END

if you have a reasonable set of items (not 100,000) to loop over you can do this:

--this will capture each Key to loop over
DECLARE @msg VARCHAR(250)
DECLARE @From   int
DECLARE @To     int
CREATE TABLE #Rows  --use a table @variable depending on the number of rows to handle
(
     RowID     int not null primary key identity(1,1)
    ,hostname  varchar(100)
)

INSERT INTO #Rows
SELECT DISTINCT hostname
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <> ''
SELECT @From=0,@To=@@ROWCOUNT

WHILE @From<@To
BEGIN
    SET @From=@From+1

    --just some example of some odd task that requires a loop
    SELECT @msg='exec master.dbo.xp_cmdshell "net send ' 
        + RTRIM(hostname) + ' '
        + 'testing  "'
        FROM #Rows 
        WHERE RowID=@From
    print @msg
    --EXEC (@msg) --<<will not actually send the messages
END