|
|
Posted by dba_222 on 08/16/06 14:15
Thank you! That worked!
One of the websites I looked at was:
http://databases.about.com/od/sqlserver/l/aastoredprocs.htm
But I can't find the other one with the loop. Now that I think of
it, the print statement was my addition.
Sorry if I'm rubbing anyone the wrong way. But I'm sure you tell, I
was getting really pissed off here. I've been working in tech for a
long time. I know that in the beginning, you have to get the basics
down. Syntax. Variables. Built in functions and other features.
Libraries. How to compile and link. How it's done in this language.
Etc. I've done this with Oracle, C, VB, some java and so on.
So, I do know what I am trying to do. And I do know that it is
possible. I'm going to the newsgroups as a last resort after
struggle. When this writer gets to that point, I just want the answer.
Just how do I do it please? Not an intellectual discussion.
Not questions or comments about what I'm trying to do.
Consider it a test question where only the right answer gets
you the marks.
Thanks again!
----------- This works:
Create procedure test2
As
Declare all_cust cursor for
Select customerid,
Companyname,
ContactName
>From customers
Declare
@Customerid varchar(5),
@Companyname varchar(40),
@ContactName varchar(30)
Begin
Select count(*)
>From customers
end
Open all_cust
Fetch all_cust
Into @customerid,
@Companyname,
@ContactName
While @@fetch_status = 0
Begin
Print ( @customerid )
Fetch next
>From all_cust
Into @customerid,
@Companyname,
@ContactName
End /* while */
Close all_cust
Deallocate all_cust
------------
Arnie Rowland wrote:
> In my many years of teaching SQL Server, I have only had to ask one person
> to leave a class and not come back. He was a Oracle person that wanted to
> argue every point, always telling me why Oracle was better. I didn't think
> that he really wanted to learn about SQL Server.
>
> Flow Control (WHILE, IF, ELSE) executes the next 'block' of code. A block of
> code is EITHER a single complete command (even if on several lines) OR
> several commands within a BEGIN...END block.
>
> WHILE does not have a END.
>
> Your code example has an END statement, leading me to believe that your
> overlooked the BEGIN in the sample code that you are working with. As Steve
> pointed out, your code is stuck in the act of endlessly printing the
> @CustomerID.
>
> Semi-Colons are optional as end of statement indicators. If you want to use
> them, please do.
>
> As indicated above, BEGIN and END statements are indicators of code block,
> containing one or more command statements. BEGIN...END blocks can be nested.
>
> I personally think that there are many, many strange and different things
> about P-SQL. Some of them intuitive to use, others not quite so. I also
> think the same about T-SQL. But when I need a screwdriver, I don't go around
> fussing about why a hammer is better. I use the correct tool for the job,
> respectful of its limitations.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> <dba_222@yahoo.com> wrote in message
> news:1155705604.376022.275910@h48g2000cwc.googlegroups.com...
> > Thanks for looking and responding.
> > Ok, This is part of what I wrote (as I posted).
> > I ran it in Query Analyser.
> >
> > The symptom, as I mentioned, was not an error.
> > It was an infinite loop. (Has no one copied, and
> > pasted it???)
> >
> > While @@fetch_status = 0
> >
> > Print @customerid
> >
> > Fetch all_cust
> > Into @customerid,
> > @Companyname,
> > @ContactName
> >
> > End /* while */
> >
> > The idea being, that the while is supposed to start at While,
> > and finish at End. Exactly what I found online.
> >
> > Are you saying that a while loop can only handle one statement??
> >
> > If not, what is the syntax to get it to execute all the code between
> > the While, and the End??? Do we give it a Begin???
> >
> > These are some of the strange things I've found with Sql Server.
> > No semicolons to indicate the end of statement.
> > And no Begin or End to indicate the start and end of a block.
> > Much more difficult to read, and to write. In Oracle, you MUST
> > have an END LOOP statement, and semicolons.
> > You must also start and end the procedure with BEGIN and END.
> > Otherwise it won't even compile.
> >
> >
> >
> >
Navigation:
[Reply to this message]
|