Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

For Each Returned row in SELECT statement

Posted on 2010-09-08
4
Medium Priority
?
238 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:ict-torquilclark
[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
  • 2
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 33625939

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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33626029
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
 
LVL 1

Author Comment

by:ict-torquilclark
ID: 33626034
doesn matter - have figured it out
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33626049
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 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