[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple Stored Procedure with WHILE Loop - Code from Query Analyzer works

Posted on 2005-04-18
3
Medium Priority
?
489 Views
Last Modified: 2008-02-01
I have a SQL statement that works in query analyzer, but I am not sure how to save it as a stored procedure.  The error message (Incorrect syntax near the keyword 'while'.) occurs when I try to use the code attached.  The stored procedure will be executed by users of an ADP file so I have the user enter the variables for the beginning and ending ticket numbers.


CODE FROM QUERY ANALYZER - this code adds tickets 12000-12500 correctly with blank values.
declare @Start_Ticket_Number int
declare @End_Ticket_Number int
select @Start_Ticket_Number = 12000
select @End_Ticket_Number = 12500
while (@Start_Ticket_Number < @End_Ticket_Number)
BEGIN
select @Start_Ticket_Number = @Start_Ticket_Number +1
insert into piticket (TicketNum, PartNum, Description, Count, WipJobNum, WipOpNum, Locator, UOM) values (@Start_Ticket_Number, '', '', '' ,'', '', '', '')
END



CODE FROM STORED PROCEDURE
Create PROCEDURE Add_Tickets(@End_Ticket_Number int, @Start_Ticket_Number int)
AS
SELECT

while (@Start_Ticket_Number < @End_Ticket_Number)
BEGIN
select @Start_Ticket_Number = @Start_Ticket_Number +1
insert into piticket (TicketNum, PartNum, Description, Count, WipJobNum, WipOpNum, Locator, UOM) values (@Start_Ticket_Number, '', '', '' ,'', '', '', '')
END
0
Comment
Question by:gj99116
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
solution46 earned 2000 total points
ID: 13806844
gj99116,

the first SELECT after the AS is unnecessary and will be causing the problems.

s46.
0
 
LVL 4

Author Comment

by:gj99116
ID: 13806945
I have been staring at this problem so long (and made the error of copying/pasting) that I can't believe how simple this solution was.  Well it is very important that I have this SP finished ASAP so it is worth the points.

Thx solution46 for pointint out my error.
0
 
LVL 9

Expert Comment

by:solution46
ID: 13806971
no probs ;)

s46.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

834 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