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

LVL 1
ict-torquilclarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:

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 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 <mystoredprocedure> <parameters>

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ict-torquilclarkAuthor Commented:
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

0
ict-torquilclarkAuthor Commented:
doesn matter - have figured it out
0
cyberkiwiCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.