尝试在SQL Server中执行真正简单的过程和循环 [英] Trying to do REALLY simple procedure and loop in SQL Server
问题描述
亲爱的专家，
再次，抱歉再次打扰你这个看似愚蠢的问题，
但我有一些非常神秘的结果这里。
ie。
创建程序the_test
As
开始
选择CustomerID
>来自dbo.customers
----------------------------- * /
服务器：消息170，级别15，状态1 ，程序the_test，第8行
第8行：客户附近的语法不正确。
请问语法是什么？????? br />
-----------------------------------------
然后，这开始是尝试用光标编写程序。
打开光标，循环，打印，结束循环。故事结束。
这实际上进入了一个无限循环!!!
谁能告诉我这里有什么问题？
创建程序test2
作为
声明all_cust游标
选择customerid，
公司名称，
联系人姓名
>来自客户
声明
@Customerid varchar（5），
@Companyname varchar（40），
@ContactName varchar（30）
开始
选择计数（*）
>来自客户
打开all_cust
获取all_cust
进入@customerid，
@Companyname，
@ContactName
而@@ fetch_status = 0
打印@customerid
获取all_cust
进入@customerid，
@Companyname，
@ContactName
结束/ *同时* /
关闭all_cust
解除分配all_cust
信不信由你，我只是在网上复制简单的程序，包括微软网站在内的
，稍加修改!!!!!!
疯狂!!!
SELECT CustomerID
来自dbo.Customers
你可能不应该尝试使用游标 - 它有非常糟糕的性能问题 - 最好的其他东西。
也许如果您要向我们提供更多信息（表格DDL，表格中的样本数据或INSERT语句），特别是对您希望实现的目标的明确解释，某人这里有助于提出更有效的非光标解决方案。
-
Arnie Rowland，博士
Westwood Consulting，Inc
最好的判断来自经验。
大多数经验都来自糟糕的判断。
- 匿名
< db ***** @ yahoo.com在消息新闻中写道：11 *************** *******@75g2000cwc.googlegro ups.com ...
尊敬的专家，
再次，抱歉再次打扰你这个看似愚蠢的问题，
但我在这里有一些非常神秘的结果。
ie。
创建程序the_test
作为
开始
选择CustomerID
>>来自dbo.customers
----------------- ------------ * /
服务器：消息170，级别15，状态1，过程the_test，第8行
第8行：不正确''customers''附近的语法。
请问语法是什么？?????
我甚至都没有尝试做任何特别的事情。
我只是想在SQL Server中找到基础知识。 />
这些表来自Northwinds模式，或者是数据库，以
的Sql Server术语。
这是其中一个你写的最经典的基本程序。
读取数据，打印，结束。第一个甚至没有使用光标，
但是甚至不编译！
如果我不能得到Sql Server到这样做，我对
使用它有很多疑问。我从来没有遇到这样的问题。
甲骨文。
我的例子是100％明确的，问题是，
正确的语法来完成简单的业务吗？
如果有人知道，请告诉我们这个秘密。
谢谢！
Arnie Rowland写道：
SELECT CustomerID
来自dbo.Customers
你可能不应该尝试使用游标 - 它有非常糟糕的性能问题 - 最好的其他东西。
也许你要向我们提供更多信息（表DDL） ，表格或INSERT语句中的样本数据，特别是对你希望实现的目标的明确解释，这里有人可以帮助提出一个更有效的非游标解决方案。
-
Arnie Rowland，博士
Westwood Consulting，Inc
最好的判断来自经验。
大多数经验来自坏人判断。
- 匿名
< db ***** @ yahoo.com在消息新闻中写道：11 ********* *************@75g2000cwc.googlegro ups.com ...
尊敬的专家，
再次，抱歉再次打扰你这个看似愚蠢的问题，
但我在这里有一些非常神秘的结果。
即。
创建程序the_test
作为
开始
选择CustomerID
>来自dbo.customers
------------- ---------------- * /
服务器：消息170，级别15，状态1，过程the_test，第8行
第8行：''customers''附近的语法不正确。
请注意语法是什么?????
查看内联评论。你是马虎，但是直言不讳。
如果你错过了复制
行，省略了开头对，你就不能指望程序可以工作。做其他基本错误。
Steve Kass
德鲁大学
< db ***** @ yahoo.com在留言新闻中写道：11 ********************** @ 75g2000cwc.googlegro ups.com ...
尊敬的专家，
再次，抱歉再次打扰你这个看似愚蠢的问题，
但是我有一些这里真的很神秘。
ie。
创建程序the_test
As
开始
选择客户ID
>>来自dbo.customers
----------------------------- * /
服务器：消息170，级别15，状态1，过程the_test，第8行
第8行：客户附近的语法不正确。
请问什么是语法？?????
我觉得你错过了关键字END。 br />
------------------------------- ----------
然后，这开始是尝试用光标编写程序。
打开光标，循环，打印，结束循环。故事结束。
这实际上进入了一个无限循环!!!
谁能告诉我这里有什么问题？
创建程序test2
作为
声明all_cust游标
选择customerid，
公司名称，
联系人名称
>>来自客户
声明
@Customerid varchar（5），
@Companyname varchar（40），
@ContactName varchar（30）
开始
选择计数（*）
>>来自客户
打开all_cust
获取all_cust
进入@customerid，
@Companyname，
@ContactName
而@@ fetch_status = 0
打印@customerid
如果取出任何内容，这是一个无限循环。打印@customerid
不会改变@@ fetch_status的值，所以语句
WHILE @@ fetch_status = 0
打印@customerid
将永远存在。
>
Fetch all_cust
进入@customerid，
@Companyname，
@ContactName
结束/ * while * /
关闭all_cust
解除分配all_cust
信不信由你，我只是从网上复制简单程序，包括微软网站在内的
，稍加修改!!!!!!
Crazy !!!
Dear experts,
Again, sorry to bother you again with such a seemingly dumb question,
but I''m having some really mysterious results here.
ie.
Create procedure the_test
As
Begin
Select CustomerID
>From dbo.customers
-----------------------------*/
Server: Msg 170, Level 15, State 1, Procedure the_test, Line 8
Line 8: Incorrect syntax near ''customers''.
Just WHAT is the syntax please?????
-----------------------------------------
Then, this started as trying to write a procedure with a cursor.
Open cursor, loop, print, end loop. End of story.
This actually goes into an infinite loop!!!
Can anyone tell me what is wrong here?
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
Open all_cust
Fetch all_cust
Into @customerid,
@Companyname,
@ContactName
While @@fetch_status = 0
Print @customerid
Fetch all_cust
Into @customerid,
@Companyname,
@ContactName
End /* while */
Close all_cust
Deallocate all_cust
Believe it or not, I''m just copying simple programs off the web,
including the Microsoft website, with slight modifications!!!!!!
Crazy!!!
SELECT CustomerID
FROM dbo.Customers
You probably shouldn''t be trying to use a cursor -it has really bad performance issues -amongst other things.
Perhaps if you were to provide us a bit more information (table DDL, sample data in the form or INSERT statements), and especially a clear explanation of what you hope to accomplish, someone here can help come up with a more efficient and non-cursor solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<db*****@yahoo.comwrote in message news:11**********************@75g2000cwc.googlegro ups.com...Dear experts,
Again, sorry to bother you again with such a seemingly dumb question,
but I''m having some really mysterious results here.
ie.
Create procedure the_test
As
Begin
Select CustomerID>>From dbo.customers-----------------------------*/
Server: Msg 170, Level 15, State 1, Procedure the_test, Line 8
Line 8: Incorrect syntax near ''customers''.
Just WHAT is the syntax please?????
I''m not even trying to do anything in particular yet.
I''m just trying to get the basics down in SQL Server.
The tables are from the Northwinds schema, or "database",
in Sql Server terms.
This is one of the most classic elementary programs you write.
Read the data, print it, end. The first one didn''t even use a cursor,
but wouldn''t even compile!
If I can''t get Sql Server to do that, I have serious doubts about
using it for much of anything. I never had issues like this in
Oracle.
My example was 100% clear, and the question was, what is the
correct syntax to do the simple business?
If anyone knows, please tell us the secret.
Thanks!
Arnie Rowland wrote:SELECT CustomerID
FROM dbo.Customers
You probably shouldn''t be trying to use a cursor -it has really bad performance issues -amongst other things.
Perhaps if you were to provide us a bit more information (table DDL, sample data in the form or INSERT statements), and especially a clear explanation of what you hope to accomplish, someone here can help come up with a more efficient and non-cursor solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<db*****@yahoo.comwrote in message news:11**********************@75g2000cwc.googlegro ups.com...Dear experts,
Again, sorry to bother you again with such a seemingly dumb question,
but I''m having some really mysterious results here.
ie.
Create procedure the_test
As
Begin
Select CustomerID>From dbo.customers-----------------------------*/
Server: Msg 170, Level 15, State 1, Procedure the_test, Line 8
Line 8: Incorrect syntax near ''customers''.
Just WHAT is the syntax please?????
See inline comments. You are being sloppy, to but it bluntly.
You can''t expect programs to work if you miss copying a
line, leave out begin-end pairs, or make other basic errors.
Steve Kass
Drew University
<db*****@yahoo.comwrote in message news:11**********************@75g2000cwc.googlegro ups.com...Dear experts,
Again, sorry to bother you again with such a seemingly dumb question,
but I''m having some really mysterious results here.
ie.
Create procedure the_test
As
Begin
Select CustomerID>>From dbo.customers
-----------------------------*/
Server: Msg 170, Level 15, State 1, Procedure the_test, Line 8
Line 8: Incorrect syntax near ''customers''.
Just WHAT is the syntax please?????
It looks to me like you are missing the keyword END.
-----------------------------------------
Then, this started as trying to write a procedure with a cursor.
Open cursor, loop, print, end loop. End of story.
This actually goes into an infinite loop!!!
Can anyone tell me what is wrong here?
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
Open all_cust
Fetch all_cust
Into @customerid,
@Companyname,
@ContactName
While @@fetch_status = 0
Print @customerid
This is an infinite loop if anything is fetched. Printing @customerid
will not change the value of @@fetch_status, so the statement
WHILE @@fetch_status = 0
PRINT @customerid
will go forever.
>
Fetch all_cust
Into @customerid,
@Companyname,
@ContactName
End /* while */
Close all_cust
Deallocate all_cust
Believe it or not, I''m just copying simple programs off the web,
including the Microsoft website, with slight modifications!!!!!!
Crazy!!!
这篇关于尝试在SQL Server中执行真正简单的过程和循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
