?
Solved

Loop Through Table executing Stored Proc for each row in table

Posted on 2005-03-08
11
Medium Priority
?
3,745 Views
Last Modified: 2008-01-09
I have a stored procedure that takes two ints for input and inserts a record into a temporary table. I need to come up with a way to loop through a table that contains several input records. Here is my example:
Table A
------------
Input 1   Input 2
1              3
2            1232
4           54646

The stored proc would then loop through the table executing the proc for each row: EXEC up_SMARTS_Contacts_GetDetail 1,3
Here is the proc code:

/*
EXEC up_SMARTS_Contacts_GetDetail (69,260114)      --//regular contact
EXEC up_SMARTS_Contacts_GetDetail @lngContactServerID = 74, @lngContactID = 32             --//potential contact
*/
CREATE PROCEDURE up_SMARTS_Contacts_GetDetail
      @lngContactServerID      INT,
      @lngContactID            INT
AS
SET NOCOUNT ON

--//Declare variables
DECLARE @blnPotential BIT

--//create tmp table to hold the recordset we're going to build
DECLARE @tmpTable TABLE
      (CUSTOMER_LOCATION_ID      INT            NULL,
      CUSTOMER_ALIAS_NM      VARCHAR(105)      NULL,
      CONTACT_SERVER_ID      INT            NOT NULL,
      CONTACT_ID            INT            NOT NULL,
      SALUTATION_DS            CHAR(4)            NULL,
      LAST_NM                  VARCHAR(35)      NULL,
      FIRST_NM            VARCHAR(35)      NULL,
      MIDDLE_INITIAL_NM      CHAR(1)            NULL,
      CONTACT_TITLE_DS      VARCHAR(30)      NULL,
      EXECUTIVE_FUNCTION_CD      SMALLINT      NULL,
      PRIORITY_FUNCTION_CD      SMALLINT      NULL,
      SL_CNTC_PERS_TP_CD      SMALLINT      NULL,
      SL_CNTC_PERS_TP_DS      VARCHAR(50)      NULL,
      TRADELANE_CD            SMALLINT      NULL,
      DECISION_MAKER_FL      BIT            NULL,
      EMAIL_ADDRESS_DS      VARCHAR(70)      NULL,
      BIRTHDAY_DT            SMALLDATETIME      NULL,
      SPOUSE_NM            VARCHAR(30)      NULL,
      SPOUSE_BIRTHDAY_DT      SMALLDATETIME      NULL,
      CHILDREN_DS            VARCHAR(255)      NULL,
      ENTERTAIN_INFO_DS      VARCHAR(255)      NULL,
      COMMENTS_DS            VARCHAR(255)      NULL,
      CREATE_CESN_ID            VARCHAR(10)      NULL,
      CREATE_DT            SMALLDATETIME      NULL,
      LAST_UPDT_CESN_ID      VARCHAR(10)      NULL,
      LAST_UPDT_DT            SMALLDATETIME      NULL,
      DATA_ACTION_TYPE_CD      SMALLINT      NULL
      )

DECLARE @tmpConFnc TABLE
(
      CUSTOMER_LOCATION_ID       INT             NOT NULL ,
      CONTACT_SERVER_ID       INT             NOT NULL ,
      CONTACT_ID             INT             NOT NULL ,
      CONTACT_FUNCTION_CD       SMALLINT       NOT NULL ,
      DATA_ACTION_TYPE_CD       SMALLINT       NULL
)

--//load primary keys for the contact(s) we're dealing with
INSERT INTO @tmpTable
      (CONTACT_SERVER_ID,
      CONTACT_ID)
VALUES
      (@lngContactServerID,
      @lngContactID)

--//Determine if this is a potential account
IF EXISTS(
      SELECT       pc.CONTACT_SERVER_ID, pc.CONTACT_ID
      FROM      POTENTIAL_CONTACT_T pc (NOLOCK)
            INNER JOIN @tmpTable keys ON (pc.CONTACT_SERVER_ID = keys.CONTACT_SERVER_ID AND pc.CONTACT_ID = keys.CONTACT_ID)
      ) BEGIN
            SET @blnPotential = 1
END
ELSE BEGIN
      SET @blnPotential = 0
END

--//contact function table
INSERT INTO @tmpConFnc
      (CUSTOMER_LOCATION_ID,
      CONTACT_SERVER_ID,
      CONTACT_ID,
      CONTACT_FUNCTION_CD,
      DATA_ACTION_TYPE_CD)
SELECT      ccf.CUSTOMER_LOCATION_ID,
      ccf.CONTACT_SERVER_ID,
      ccf.CONTACT_ID,
      ccf.CONTACT_FUNCTION_CD,
      ccf.DATA_ACTION_TYPE_CD
FROM      CUST_CONTACT_FNC_T ccf (NOLOCK)
      INNER JOIN @tmpTable keys ON (ccf.CONTACT_SERVER_ID = keys.CONTACT_SERVER_ID AND ccf.CONTACT_ID = keys.CONTACT_ID)


--IF (@blnPotential = 0) BEGIN
            --//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            --//                                    CORE CUSTOMER
            --//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            UPDATE      @tmpTable
            SET       CUSTOMER_LOCATION_ID = f.CUSTOMER_LOCATION_ID,
                  CUSTOMER_ALIAS_NM = cl.CUSTOMER_ALIAS_NM,
                  SALUTATION_DS = c.SALUTATION_DS,
                  LAST_NM = c.LAST_NM,
                  FIRST_NM = c.FIRST_NM,
                  MIDDLE_INITIAL_NM = c.MIDDLE_INITIAL_NM,
                  CONTACT_TITLE_DS = c.CONTACT_TITLE_DS,
                  EXECUTIVE_FUNCTION_CD = p.CONTACT_FUNCTION_CD,
                  SL_CNTC_PERS_TP_CD = p.SL_CNTC_PERS_TP_CD,
                  SL_CNTC_PERS_TP_DS = pt.SL_CNTC_PERS_TP_DS,
                  DECISION_MAKER_FL = p.DECISION_MAKER_FL,
                  EMAIL_ADDRESS_DS = c.EMAIL_ADDRESS_DS,
                  BIRTHDAY_DT = p.BIRTHDAY_DT,
                  SPOUSE_NM = p.SPOUSE_NM,
                  SPOUSE_BIRTHDAY_DT = p.SPOUSE_BIRTHDAY_DT,
                  CHILDREN_DS = p.CHILDREN_DS,
                  ENTERTAIN_INFO_DS = p.ENTERTAIN_INFO_DS,
                  COMMENTS_DS = p.COMMENTS_DS,
                  CREATE_CESN_ID = c.CREATE_CESN_ID,
                  CREATE_DT = c.CREATE_DT,
                  LAST_UPDT_CESN_ID = c.LAST_UPDT_CESN_ID,
                  LAST_UPDT_DT = c.LAST_UPDT_DT,
                  DATA_ACTION_TYPE_CD = c.DATA_ACTION_TYPE_CD
            FROM      CONTACT_T c (NOLOCK)
                  LEFT JOIN SALES_CONTACT_PERSONAL_T p (NOLOCK) ON (c.CONTACT_SERVER_ID = p.CONTACT_SERVER_ID AND c.CONTACT_ID = p.CONTACT_ID)
                  LEFT JOIN SALES_CONTACT_PERSONAL_TYPE_T pt (NOLOCK) ON (p.SL_CNTC_PERS_TP_CD = pt.SL_CNTC_PERS_TP_CD)
                  LEFT JOIN @tmpConFnc f ON (c.CONTACT_SERVER_ID = f.CONTACT_SERVER_ID AND c.CONTACT_ID = f.CONTACT_ID)
                  LEFT JOIN CUSTOMER_LIST_T cl (NOLOCK) ON (f.CUSTOMER_LOCATION_ID = cl.CUSTOMER_LOCATION_ID)
                  INNER JOIN @tmpTable keys ON (c.CONTACT_SERVER_ID = keys.CONTACT_SERVER_ID AND c.CONTACT_ID = keys.CONTACT_ID)
            WHERE       ISNULL(c.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      f.CONTACT_FUNCTION_CD = 28
            AND      @blnPotential = 0 --//CORE CUSTOMER

            --//Determine if Contact is a Priority contact
            UPDATE @tmpTable
            SET      PRIORITY_FUNCTION_CD = f.CONTACT_FUNCTION_CD
            FROM       @tmpTable tmp
                  INNER JOIN @tmpConFnc cf ON (tmp.CONTACT_ID = cf.CONTACT_ID AND tmp.CONTACT_SERVER_ID = cf.CONTACT_SERVER_ID)
                  INNER JOIN CONTACT_FUNCTION_T f (NOLOCK) ON (cf.CONTACT_FUNCTION_CD = f.CONTACT_FUNCTION_CD)
            WHERE       ISNULL(cf.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      ISNULL(tmp.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      cf.CONTACT_FUNCTION_CD = 42 --//(42)-Priority
            AND      @blnPotential = 0 --//CORE CUSTOMER

            --//Determine Tradelane contact is associated with
            UPDATE @tmpTable
            SET      TRADELANE_CD = f.CONTACT_FUNCTION_CD
            FROM       @tmpTable tmp
                  INNER JOIN @tmpConFnc cf ON (tmp.CONTACT_ID = cf.CONTACT_ID AND tmp.CONTACT_SERVER_ID = cf.CONTACT_SERVER_ID)
                  INNER JOIN CONTACT_FUNCTION_T f (NOLOCK) ON (cf.CONTACT_FUNCTION_CD = f.CONTACT_FUNCTION_CD)
            WHERE       ISNULL(cf.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      ISNULL(tmp.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      cf.CONTACT_FUNCTION_CD IN (30,31,32,33,41) --//(30)-Puerto Rico (31)-Alaska (32)-Hawaii (33)-NA (41)Guam
            AND      @blnPotential = 0 --//CORE CUSTOMER
--END
--ELSE BEGIN
            --//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            --//                              POTENTIAL CUSTOMER
            --//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            UPDATE      @tmpTable
            SET       CUSTOMER_LOCATION_ID = xref.POTENTIAL_CUSTOMER_ID,
                  CUSTOMER_ALIAS_NM = pc.POTENTIAL_ALIAS_NM,
                  SALUTATION_DS = c.SALUTATION_DS,
                  LAST_NM = c.LAST_NM,
                  FIRST_NM = c.FIRST_NM,
                  MIDDLE_INITIAL_NM = c.MIDDLE_INITIAL_NM,
                  CONTACT_TITLE_DS = c.CONTACT_TITLE_DS,
                  EXECUTIVE_FUNCTION_CD = p.CONTACT_FUNCTION_CD,
                  SL_CNTC_PERS_TP_CD = p.SL_CNTC_PERS_TP_CD,
                  SL_CNTC_PERS_TP_DS = pt.SL_CNTC_PERS_TP_DS,
                  DECISION_MAKER_FL = p.DECISION_MAKER_FL,
                  EMAIL_ADDRESS_DS = c.EMAIL_ADDRESS_DS,
                  BIRTHDAY_DT = p.BIRTHDAY_DT,
                  SPOUSE_NM = p.SPOUSE_NM,
                  SPOUSE_BIRTHDAY_DT = p.SPOUSE_BIRTHDAY_DT,
                  CHILDREN_DS = p.CHILDREN_DS,
                  ENTERTAIN_INFO_DS = p.ENTERTAIN_INFO_DS,
                  COMMENTS_DS = p.COMMENTS_DS,
                  CREATE_CESN_ID = c.CREATE_CESN_ID,
                  CREATE_DT = c.CREATE_DT,
                  LAST_UPDT_CESN_ID = c.LAST_UPDT_CESN_ID,
                  LAST_UPDT_DT = c.LAST_UPDT_DT,
                  DATA_ACTION_TYPE_CD = c.DATA_ACTION_TYPE_CD
            FROM      CONTACT_T c (NOLOCK)
                  LEFT JOIN SALES_CONTACT_PERSONAL_T p (NOLOCK) ON (c.CONTACT_SERVER_ID = p.CONTACT_SERVER_ID AND c.CONTACT_ID = p.CONTACT_ID)
                  LEFT JOIN SALES_CONTACT_PERSONAL_TYPE_T pt (NOLOCK) ON (p.SL_CNTC_PERS_TP_CD = pt.SL_CNTC_PERS_TP_CD)
                  LEFT JOIN POTENTIAL_CONTACT_T xref (NOLOCK) ON (c.CONTACT_SERVER_ID = xref.CONTACT_SERVER_ID AND c.CONTACT_ID = xref.CONTACT_ID)
                  LEFT JOIN POTENTIAL_CUSTOMER_T pc (NOLOCK) ON (xref.POTENTIAL_CUSTOMER_ID = pc.POTENTIAL_CUSTOMER_ID)
                  INNER JOIN @tmpTable keys ON (c.CONTACT_SERVER_ID = keys.CONTACT_SERVER_ID AND c.CONTACT_ID = keys.CONTACT_ID)
            WHERE       ISNULL(c.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      xref.CONTACT_FUNCTION_CD = 28
            AND      @blnPotential = 1 --//POTENTIAL CUSTOMER

            --//Determine if Contact is a Priority contact
            UPDATE @tmpTable
            SET      PRIORITY_FUNCTION_CD = f.CONTACT_FUNCTION_CD
            FROM       @tmpTable tmp
                  INNER JOIN POTENTIAL_CONTACT_T xref (NOLOCK) ON (tmp.CONTACT_ID = xref.CONTACT_ID AND tmp.CONTACT_SERVER_ID = xref.CONTACT_SERVER_ID)
                  INNER JOIN CONTACT_FUNCTION_T f (NOLOCK) ON (xref.CONTACT_FUNCTION_CD = f.CONTACT_FUNCTION_CD)
            WHERE       ISNULL(tmp.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      xref.CONTACT_FUNCTION_CD = 42 --//(42)-Priority
            AND      @blnPotential = 1 --//POTENTIAL CUSTOMER

            --//Determine Tradelane contact is associated with
            UPDATE @tmpTable
            SET      TRADELANE_CD = f.CONTACT_FUNCTION_CD
            FROM       @tmpTable tmp
                  INNER JOIN POTENTIAL_CONTACT_T xref (NOLOCK) ON (tmp.CONTACT_ID = xref.CONTACT_ID AND tmp.CONTACT_SERVER_ID = xref.CONTACT_SERVER_ID)
                  INNER JOIN CONTACT_FUNCTION_T f (NOLOCK) ON (xref.CONTACT_FUNCTION_CD = f.CONTACT_FUNCTION_CD)
            WHERE       ISNULL(tmp.DATA_ACTION_TYPE_CD,0) NOT IN (3,6) --//Prevent retrieving records tagged for deletion
            AND      xref.CONTACT_FUNCTION_CD IN (30,31,32,33,41) --//(30)-Puerto Rico (31)-Alaska (32)-Hawaii (33)-NA (41)Guam
            AND      @blnPotential = 1 --//POTENTIAL CUSTOMER
--END


--//Retrieve resultset built in temporary table
SELECT tmp.CUSTOMER_LOCATION_ID,
      tmp.CUSTOMER_ALIAS_NM,
      tmp.CONTACT_SERVER_ID,       
      tmp.CONTACT_ID,
      tmp.SALUTATION_DS,
      tmp.LAST_NM,
      tmp.FIRST_NM,
      tmp.MIDDLE_INITIAL_NM,
      tmp.CONTACT_TITLE_DS,
      tmp.EXECUTIVE_FUNCTION_CD AS 'CONTACT_FUNCTION_CD',
      tmp.PRIORITY_FUNCTION_CD,
      tmp.SL_CNTC_PERS_TP_CD,
      tmp.SL_CNTC_PERS_TP_DS,
      tmp.TRADELANE_CD,
      tmp.DECISION_MAKER_FL,
      tmp.EMAIL_ADDRESS_DS,
      tmp.BIRTHDAY_DT,
      tmp.SPOUSE_NM,
      tmp.SPOUSE_BIRTHDAY_DT,
      tmp.CHILDREN_DS,
      tmp.ENTERTAIN_INFO_DS,
      tmp.COMMENTS_DS,
      tmp.CREATE_CESN_ID,
      tmp.CREATE_DT,
      tmp.LAST_UPDT_CESN_ID,
      tmp.LAST_UPDT_DT
FROM      @tmpTable tmp





GO


I am desperate please help!


0
Comment
Question by:infutech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13492665
use a cursor or convert this stored procedure to a user-defined-function that can be called "inline"
0
 

Author Comment

by:infutech
ID: 13492727
can you suggest some ways that I might use a cursor? I am stuck.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13492785
if you could give me the query that you wish to run the procedure on i can certainly do that.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 5

Expert Comment

by:xnmb
ID: 13492889
1) if the table doesn't have an identity column yet, create one:
alter table A
add RowID int identity
go

2) create an SP that would loop through table A:

create proc sp_LoopTableA
as
declare @x int,
@Input1 int,
@Input2 int

set @x=1

while @x<(select count(*) from A)+1
begin
select @Input1=Input1,@Input2=Input2
from A
where RowID=@x

exec up_SMARTS_Contacts_GetDetail @Input1,@Input2

set @x=@x+1
end
go

3) execute sp_LoopTableA

this may not be exactly what you're looking for, but you get the idea


0
 

Author Comment

by:infutech
ID: 13493068
Basically I am trying to do this:
fetch contact_server_id and contact id from info_t
assign to  variables
EXEC up_SMARTS_Contacts_GetDetail (contact_server_id, contact id )
insert output into table
repeat until it has processed each row contained in info_t

It is about 120,000 rows of data
0
 
LVL 5

Accepted Solution

by:
xnmb earned 2000 total points
ID: 13493519
if that's the case then perhaps this (modified) SP would do the job:

create proc sp_LoopTableA
as
declare @x int,
@Input1 int,
@Input2 int

create table #Temp
(CUSTOMER_LOCATION_ID     INT          NULL,
CUSTOMER_ALIAS_NM     VARCHAR(105)     NULL,
CONTACT_SERVER_ID     INT          NOT NULL,
CONTACT_ID          INT          NOT NULL,
SALUTATION_DS          CHAR(4)          NULL,
LAST_NM               VARCHAR(35)     NULL,
FIRST_NM          VARCHAR(35)     NULL,
MIDDLE_INITIAL_NM     CHAR(1)          NULL,
CONTACT_TITLE_DS     VARCHAR(30)     NULL,
EXECUTIVE_FUNCTION_CD     SMALLINT     NULL,
PRIORITY_FUNCTION_CD     SMALLINT     NULL,
SL_CNTC_PERS_TP_CD     SMALLINT     NULL,
SL_CNTC_PERS_TP_DS     VARCHAR(50)     NULL,
TRADELANE_CD          SMALLINT     NULL,
DECISION_MAKER_FL     BIT          NULL,
EMAIL_ADDRESS_DS     VARCHAR(70)     NULL,
BIRTHDAY_DT          SMALLDATETIME     NULL,
SPOUSE_NM          VARCHAR(30)     NULL,
SPOUSE_BIRTHDAY_DT     SMALLDATETIME     NULL,
CHILDREN_DS          VARCHAR(255)     NULL,
ENTERTAIN_INFO_DS     VARCHAR(255)     NULL,
COMMENTS_DS          VARCHAR(255)     NULL,
CREATE_CESN_ID          VARCHAR(10)     NULL,
CREATE_DT          SMALLDATETIME     NULL,
LAST_UPDT_CESN_ID     VARCHAR(10)     NULL,
LAST_UPDT_DT          SMALLDATETIME     NULL,
DATA_ACTION_TYPE_CD     SMALLINT     NULL
)

set @x=1

while @x<(select count(*) from A)+1
begin
select @Input1=Input1,@Input2=Input2
from A
where RowID=@x

insert into #Temp
exec up_SMARTS_Contacts_GetDetail @Input1,@Input2

set @x=@x+1

select *
from #Temp
end
go
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13494435
xnmb has the right idea, however RowID is used in oracle and is not available in SQL
the looping thus would require the cursor

declare curLoop CURSOR for
SELECT  Input1, Input2
FROM tableA
OPEN  curLOOP
Fetch next from curLoop INTO @Input1, @input2
WHILE @@fetch_status = 0
  begin
    insert into #Temp
    exec up_SMARTS_Contacts_GetDetail @Input1,@Input2
    Fetch next from curLoop INTO @Input1, @input2
  end
CLOSE curLoop
DEALLOCATE curLoop
0
 
LVL 5

Expert Comment

by:xnmb
ID: 13494449
Hi Karin (my idol), you missed to read my earlier post:

>>1) if the table doesn't have an identity column yet, create one:
>>alter table A
>>add RowID int identity
>>go
0
 
LVL 5

Expert Comment

by:xnmb
ID: 13494462
btw, in my last post,

select *
from #Temp

should be outside the loop =)
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13494517
hi xnmb sorry didnt read fully  :)
0
 

Author Comment

by:infutech
ID: 13532093
This was exactly what I needed. So good I forgot to come back and accept it!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question