CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2008-r2
Oisian
Oisian

Reputation: 45

Looping through SQL Server table, running stored procedure for the row, and put results in to a table

I have a SQL Server stored procedure for getting an employee's clocked hours over a few days. The procedure uses a card code value for getting the information about the employee.

What I want to do is select all of the CardCodes from one table, then loop through each record running the stored procedure and then making a table with the results of each one. so by the end there will be one table with each employees worked times.

Query for getting the list of CardCodes:

SELECT     
    CardCode
FROM
    CHINA_VISION_PubCards

The produces a list like this. 

007b7aaf
00cf77b6
00cf9200
00cf9f40
007B6FFB
00d398dd
00cf4673

And so on.

I can run the stored procesdure inside SQL Server like this. 

EXEC [dbo].[getPast5DaysWorkedHours] @CardCode = N'007da097'

Where 007da097 is the employee's card code.

This produces a result like this:

Employee Name | CardCode  |  Clock No  | Date   |  Seconds |  Time  
     Name       007da097      005718    2015.10.16  28761   07:59:21
     Name       007da097      005718    2015.10.21  28991   08:03:11
     Name       007da097      005718    2015.10.23  29768   08:16:08
     Name       007da097      005718    2015.10.28  29441   08:10:41

To reiterate what I am trying to do: I want to loop through the list of card codes, and for each one of them run the stored procedure with the cardCode value. And then put the results of each procedure call into one table.

Upvotes: 1

Views: 8960

Answers (2)

mikurski
mikurski

Reputation: 1363

You can use a WHILE loop to go through every entry, without using a cursor or repeated SELECT and DELETEs:

DECLARE @store table (id bigint identity(1,1), CardCode (your cardcode column type here))

insert into @store (CardCode)
SELECT DISTINCT     
    CardCode
FROM
    CHINA_VISION_PubCards

declare @idx bigint = 1
declare @max bigint = (select MAX(id) from @store)

WHILE @idx<=@max
BEGIN
EXEC [dbo].[getPast5DaysWorkedHours] @CardCode = (select top 1 CardCode from @store where id = @idx)

set @idx = @idx+1

END

The comments are right, though - it's a lot nicer if you can use a table-valued parameter to pass through a set of values.

Upvotes: 2

ClearLogic
ClearLogic

Reputation: 3682

SQL has while loop

While Loop T-SQL

You can do somthing like this. (Untested)

IF OBJECT_ID('TempDB..#TEMP') IS NOT NULL DROP TABLE #TEMP
SELECT  distinct  CardCode
INTO #TEMP
FROM CHINA_VISION_PubCards

WHILE ( (select count(*) FROM #TEMP) > 0 )
    BEGIN
         DECLARE @CardCode varchar(50)
         SELECT TOP 1 @CardCode=CardCode FROM #TEMP

         INSERT INTO TableTarget
         EXEC [dbo].[getPast5DaysWorkedHours] @CardCode

         DELETE FROM #TEMP WHERE CardCode = @CardCode
    END

Upvotes: 6

Related Questions