infutech
asked on
Loop Through Table executing Stored Proc for each row in table
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_GetDeta
Here is the proc code:
/*
EXEC up_SMARTS_Contacts_GetDeta
EXEC up_SMARTS_Contacts_GetDeta
*/
CREATE PROCEDURE up_SMARTS_Contacts_GetDeta
@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_TYP
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_
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
AND ISNULL(tmp.DATA_ACTION_TYP
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
AND ISNULL(tmp.DATA_ACTION_TYP
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_TYP
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_I
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_
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_TYP
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_TYP
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!
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_GetDeta
Here is the proc code:
/*
EXEC up_SMARTS_Contacts_GetDeta
EXEC up_SMARTS_Contacts_GetDeta
*/
CREATE PROCEDURE up_SMARTS_Contacts_GetDeta
@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_TYP
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_
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
AND ISNULL(tmp.DATA_ACTION_TYP
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
AND ISNULL(tmp.DATA_ACTION_TYP
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_TYP
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_I
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_
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_TYP
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_TYP
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!
Brian Crowe
use a cursor or convert this stored procedure to a user-defined-function that can be called "inline"
ASKER
can you suggest some ways that I might use a cursor? I am stuck.
if you could give me the query that you wish to run the procedure on i can certainly do that.
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=Inp
from A
where RowID=@x
exec up_SMARTS_Contacts_GetDeta
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
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=Inp
from A
where RowID=@x
exec up_SMARTS_Contacts_GetDeta
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
ASKER
Basically I am trying to do this:
fetch contact_server_id and contact id from info_t
assign to variables
EXEC up_SMARTS_Contacts_GetDeta
insert output into table
repeat until it has processed each row contained in info_t
It is about 120,000 rows of data
fetch contact_server_id and contact id from info_t
assign to variables
EXEC up_SMARTS_Contacts_GetDeta
insert output into table
repeat until it has processed each row contained in info_t
It is about 120,000 rows of data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_GetDeta
Fetch next from curLoop INTO @Input1, @input2
end
CLOSE curLoop
DEALLOCATE curLoop
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_GetDeta
Fetch next from curLoop INTO @Input1, @input2
end
CLOSE curLoop
DEALLOCATE curLoop
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
>>1) if the table doesn't have an identity column yet, create one:
>>alter table A
>>add RowID int identity
>>go
btw, in my last post,
select *
from #Temp
should be outside the loop =)
select *
from #Temp
should be outside the loop =)
hi xnmb sorry didnt read fully :)
ASKER
This was exactly what I needed. So good I forgot to come back and accept it!