Solved

For Each Returned row in SELECT statement

Posted on 2010-09-08
4
223 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
  • 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now