For Loop in Stored Procedure

  • Thread starter Thread starter Can
  • Start date Start date
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
 
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...
 
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...
 
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...
 
Back
Top