foreach in SQL

Hi all,

I want to run a SP (shown below)...

for every row in

SELECT * FROM mainDB WHERE X = 'hi'
(say for example this brings in 2000 rows)

feeding in 2 columns of variables...

STORED PROCEDURE......
EXEC dbo.DailyPositions_ProductContractTimePeriod 
@Product = (COLUMN1 of main select statement), 
@AccountID = 'D6323759-1E95-4354-85A1-7040EA6F5FD8', 
@ContractDate = (COLUMN2 of main select statement), 
@StartDate = '20130510', 
@EndDate = '20130510'

Open in new window



can someone point me in the right direction?

I see things online like PIVOT/CURSOR/ROWNEXT..

thanks
solarissfAsked:
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.

dsackerContract ERP Admin/ConsultantCommented:
You'll have to run that via a CURSOR:
DECLARE @COLUMN1    varchar(80),    -- or whatever is your true datatype
        @COLUMN2    varchar(80)     -- or whatever is your true datatype

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT COLUMN1, COLUMN2 FROM mainDB WHERE X = 'hi' 

OPEN myCursor

WHILE 1 = 1
BEGIN
    FETCH FROM myCursor INTO @COLUMN1, @COLUMN2
    IF @@FETCH_STATUS <> 0 BREAK

    EXEC dbo.DailyPositions_ProductContractTimePeriod 
        @Product = @COLUMN1, 
        @AccountID = 'D6323759-1E95-4354-85A1-7040EA6F5FD8', 
        @ContractDate = @COLUMN2, 
        @StartDate = '20130510', 
        @EndDate = '20130510'
END

CLOSE myCursor
DEALLOCATE myCursor

Open in new window

0
Ross TurnerManagement Information Support AnalystCommented:
Hi solarissf

i create the below example script... which is basically a loop with dynamic sql

1) select your criteria into a temp table giving it an identity field e.g id
2) uses count on the temptable to find the amount of loops.
3) assigns the column1 / column2 on each pass based of the incrementing id

if you use this in your test enviroment it will just print the dynamic sql.

but if you want it to execute just uncomment the -- exec (@sql)


Set nocount on 

IF OBJECT_ID('tempdb..#mainDB ') IS NOT NULL
DROP TABLE #mainDB 
Create Table #mainDB 
( Column1 varchar(30),
  Column2 varchar(30)
)

INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test1','Column2-Test1');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test2','Column2-Test2');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test3','Column2-Test3');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test4','Column2-Test4');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test5','Column2-Test5');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test6','Column2-Test6');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test7','Column2-Test7');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test8','Column2-Test8');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test9','Column2-Test9');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test10','Column2-Test10');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test11','Column2-Test11');
INSERT INTO #mainDB(Column1,Column2) VALUES('Column1-Test12','Column2-Test12');



IF OBJECT_ID('tempdb..#TempTableLoop') IS NOT NULL
DROP TABLE #TempTableLoop
Create Table #TempTableLoop(
ID int identity, 
Column1 varchar(30),
Column2 varchar(30)
)

INSERT INTO #TempTableLoop (Column1,Column2)
SELECT  *
FROM    #mainDB
-- where what ever your criteria

DECLARE @Count	int
DECLARE @SQL	Varchar(8000)
DECLARE @COLUMN1	Varchar(8000)
DECLARE @COLUMN2	Varchar(8000)
SET		@Count = 1

While @count < (Select count(*) from #TempTableLoop)
Begin 
Set @COLUMN1 = (select column1 from #TempTableLoop where id = @Count)
Set @COLUMN2 = (select column2 from #TempTableLoop where id = @Count)

Set @Sql =  '
		EXEC dbo.DailyPositions_ProductContractTimePeriod 
        @Product =''' + @COLUMN1 + '''
		@AccountID = ''D6323759-1E95-4354-85A1-7040EA6F5FD8''' +
        ',@ContractDate =''' + @COLUMN2 + '''' +
		',@StartDate = ''20130510''' +
		',@EndDate = ''20130510'''
        
print @sql
-- exec (@sql)

Set @Count = @Count + 1
End

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
hnasrCommented:
I assume, the objective is:

1 get 1st record in a table.
2 assign:  @c1 to col1 and @c2 to col2 of this record.
3 Use @c1, @c2 in some operation.
4 move to next record
5 repeat from step 2 until end of records.

Is this correct?
0
solarissfAuthor Commented:
thanks guys... appreciate the help
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.