For Loop in Stored Procedure

C

Can

I have this for loop in a stored procedure to do some
testing of the SQL server. I want to add 3000 records.

I get the following error
ADO error: Incorrect Syntax near the keyword 'FOR' Line
46: Incorrect syntax near 'Next'

Alter Procedure "Massive_Merge"

@i int =1

As

BEGIN TRANSACTION

For @i = 1 To 300
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004',
1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003',
2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh
yeah!', '03/03/2002', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'guru', '04/04/2001', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'queen', '05/05/2000', 5)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'amazing', '06/06/1999', 1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'cool!', '07/07/1998', 2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'awesome!', '08/08/1997', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super
cool', '09/09/1996', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'Beth', 'Donovan', '10/10/1995', 5)
Next

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not
completed!', 16, -1)
ROLLBACK TRANSACTION
END

COMMIT TRANSACTION

/* set nocount on */
return
 
G

Graham R Seach

You can't use a For...Next construct in T-SQL. Instead, you need to use a
While...End construct.

DECLARE @i INT
SET @i = 1

BEGIN TRANSACTION

WHILE @i < 300
BEGIN
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004', 1)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003', 2)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh yeah!', '03/03/2002', 3)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'database', 'guru', '04/04/2001', 4)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'database', 'queen', '05/05/2000', 5)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'amazing', '06/06/1999', 1)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'cool!', '07/07/1998', 2)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'awesome!', '08/08/1997', 3)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super cool', '09/09/1996', 4)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'Beth', 'Donovan', '10/10/1995', 5)

SET @i = @i + 1
END

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not completed!', 16, -1)
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
END

/* set nocount on */
RETURN

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Can said:
I have replaced

@i int =1

with

DECLARE @i int
SET @i = 1

but it still does not work.

-----Original Message-----

I have this for loop in a stored procedure to do some
testing of the SQL server. I want to add 3000 records.

I get the following error
ADO error: Incorrect Syntax near the keyword 'FOR' Line
46: Incorrect syntax near 'Next'

Alter Procedure "Massive_Merge"

@i int =1

As

BEGIN TRANSACTION

For @i = 1 To 300
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004',
1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003',
2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh
yeah!', '03/03/2002', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'guru', '04/04/2001', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'queen', '05/05/2000', 5)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'amazing', '06/06/1999', 1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'cool!', '07/07/1998', 2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'awesome!', '08/08/1997', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super
cool', '09/09/1996', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'Beth', 'Donovan', '10/10/1995', 5)
Next

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not
completed!', 16, -1)
ROLLBACK TRANSACTION
END

COMMIT TRANSACTION

/* set nocount on */
return

.
Click to expand...
Click to expand...
 
C

Can

It works great. Thanks.

Are there any websites / books you recommend on things you
can or can not do in T-SQL?
-----Original Message-----
You can't use a For...Next construct in T-SQL. Instead, you need to use a
While...End construct.

DECLARE @i INT
SET @i = 1

BEGIN TRANSACTION

WHILE @i < 300
BEGIN
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004', 1)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003', 2)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh yeah!', '03/03/2002', 3)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'database', 'guru', '04/04/2001', 4)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'database', 'queen', '05/05/2000', 5)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'amazing', '06/06/1999', 1)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'cool!', '07/07/1998', 2)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'awesome!', '08/08/1997', 3)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super cool', '09/09/1996', 4)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'Beth', 'Donovan', '10/10/1995', 5)

SET @i = @i + 1
END

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not completed!', 16, -1)
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
END

/* set nocount on */
RETURN

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


I have replaced

@i int =1

with

DECLARE @i int
SET @i = 1

but it still does not work.

-----Original Message-----

I have this for loop in a stored procedure to do some
testing of the SQL server. I want to add 3000 records.

I get the following error
ADO error: Incorrect Syntax near the keyword 'FOR' Line
46: Incorrect syntax near 'Next'

Alter Procedure "Massive_Merge"

@i int =1

As

BEGIN TRANSACTION

For @i = 1 To 300
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004',
1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003',
2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh
yeah!', '03/03/2002', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'guru', '04/04/2001', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'queen', '05/05/2000', 5)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'amazing', '06/06/1999', 1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'cool!', '07/07/1998', 2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'awesome!', '08/08/1997', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super
cool', '09/09/1996', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'Beth', 'Donovan', '10/10/1995', 5)
Next

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not
completed!', 16, -1)
ROLLBACK TRANSACTION
END

COMMIT TRANSACTION

/* set nocount on */
return

.
Click to expand...
Click to expand...


.
Click to expand...
 
G

Graham R Seach

http://www.oreilly.com/catalog/wintrnssql/chapter/ch01.html
http://www.oreilly.com/catalog/transqlcook/index.html

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Can said:
It works great. Thanks.

Are there any websites / books you recommend on things you
can or can not do in T-SQL?
-----Original Message-----
You can't use a For...Next construct in T-SQL. Instead, you need to use a
While...End construct.

DECLARE @i INT
SET @i = 1

BEGIN TRANSACTION

WHILE @i < 300
BEGIN
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004', 1)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003', 2)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh yeah!', '03/03/2002', 3)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'database', 'guru', '04/04/2001', 4)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'database', 'queen', '05/05/2000', 5)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'amazing', '06/06/1999', 1)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'cool!', '07/07/1998', 2)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'awesome!', '08/08/1997', 3)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super cool', '09/09/1996', 4)

INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'Beth', 'Donovan', '10/10/1995', 5)

SET @i = @i + 1
END

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not completed!', 16, -1)
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
END

/* set nocount on */
RETURN

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html


I have replaced

@i int =1

with

DECLARE @i int
SET @i = 1

but it still does not work.


-----Original Message-----

I have this for loop in a stored procedure to do some
testing of the SQL server. I want to add 3000 records.

I get the following error
ADO error: Incorrect Syntax near the keyword 'FOR' Line
46: Incorrect syntax near 'Next'

Alter Procedure "Massive_Merge"

@i int =1

As

BEGIN TRANSACTION

For @i = 1 To 300
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('123', 'Can', 'SQL', '01/01/2004',
1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('ABC', 'Can', 'SQL', '02/02/2003',
2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'rocks!', 'Oh
yeah!', '03/03/2002', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'guru', '04/04/2001', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'database', 'queen', '05/05/2000', 5)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'amazing', '06/06/1999', 1)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'cool!', '07/07/1998', 2)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'is', 'awesome!', '08/08/1997', 3)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES ('Candace', 'is', 'super
cool', '09/09/1996', 4)
INSERT Table1
([123], sadf, asdf, Admitdate, Details5)
VALUES
('Candace', 'Beth', 'Donovan', '10/10/1995', 5)
Next

IF @@ERROR <> 0
BEGIN
RAISERROR ('Error, transaction not
completed!', 16, -1)
ROLLBACK TRANSACTION
END

COMMIT TRANSACTION

/* set nocount on */
return

.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Obtain Month & Year from a Text formatted Date field using Left Fu 2
How to solve a complex problem using Excel 2003 2
Access Insert - Syntax Error for field "DATE" but not for "DATE1" 2
Complex function needed? 5
Problem Event Name: CLR20r3+SSMS.exe 0
Weird situation - Stored procedure executed twice 2
Special sort for oldest and newest date 1
Stored Procedure (Using a variable in FROM Clause) 5

Top