asked on

For Each Returned row in SELECT statement

I have written the following sql code (see attatched)

For Each Row it returns is want to assign the relevent fields to the relevent variables and run an exec command (to run a SP)

how can i do this?

DECLARE @Tel NVARCHAR(20)
DECLARE @Tel2 NVARCHAR(20)
DECLARE @Tel3 NVARCHAR(20)
DECLARE @Field1 NVARCHAR(20)
DECLARE @Field5 NVARCHAR(20)
DECLARE @Field6 NVARCHAR(20)
DECLARE @CallBack DATETIME

SELECT D.Telephone,D.Telephone2,D.Telephone3,D.Display,D.Field5,D.Field6,C.DateToCallBack
FROM Dialler1.DBO."Doris Leads" D
INNER JOIN Callbacks C
ON D.ID = C.CrossRefID

Open in new window

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ict-torquilclark

ASKER

I have attatched my full code

I get the following error

Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@Tel1".

any ideas?

DECLARE @Tel NVARCHAR(20)
DECLARE @Tel2 NVARCHAR(20)
DECLARE @Tel3 NVARCHAR(20)
DECLARE @Field1 NVARCHAR(20)
DECLARE @Field5 NVARCHAR(20)
DECLARE @Field6 NVARCHAR(20)
DECLARE @CallBack DATETIME

DECLARE MYCURSOR CURSOR FOR 
	SELECT D.Telephone,D.Telephone2,D.Telephone3,D.Display,D.Field5,D.Field6,C.DateToCallBack
	FROM Dialler1.DBO."Doris Leads" D
	INNER JOIN Dialler1.DBO.Callbacks C
	ON D.ID = C.CrossRefID
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @Tel,@Tel2,@Tel3,@Field1,@Field5,@Field6,@CallBack
WHILE @@FETCH_STATUS = 0
BEGIN

	EXEC ApplicationServerDialler.DBO.InsertCampaignRecord 'Doris Leads',@Tel1,@Tel2,@Tel3,@Field1,,,,@Field5,@Field6,,,,,,,,,,,,,,,,,,,,,,,,,@Callback,,,,,,

	FETCH NEXT FROM MYCURSOR INTO @Tel,@Tel2,@Tel3,@Field1,@Field5,@Field6,@CallBack
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

Open in new window

ict-torquilclark

ASKER

doesn matter - have figured it out

cyberkiwi

Change line 19 from

EXEC ApplicationServerDialler.DBO.InsertCampaignRecord 'Doris Leads',@Tel1,@Tel2,@Tel3,@Field1,,,,@Field5,@Field6,,,,,,,,,,,,,,,,,,,,,,,,,@Callback,,,,,,

to

EXEC ApplicationServerDialler.DBO.InsertCampaignRecord 'Doris Leads',@Tel,@Tel2,@Tel3,@Field1,,,,@Field5,@Field6,,,,,,,,,,,,,,,,,,,,,,,,,@Callback,,,,,,

Because you have declared @Tel, @Tel2, @Tel3 but no @Tel1.
FWIW, SQL Server is not access, you cannot "skip over" parameters with multiple commas like that.
If you intend to pass only some parameters, you must explicitly name them, e.g.

EXEC myproc @Tel1=@Tel, @2ndParam=@Tel2, @Param3=@Tel3.... etc