Solved

For Each Returned row in SELECT statement

Posted on 2010-09-08
4
233 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 500 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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