在 SQLFiddle 中使用过程访问时遇到问题 [英] Trouble accessing with Procedure in SQLFiddle

问题描述

我正在使用 SQL Server 2008 在 SQLFiddle 中构建一个查询.这个查询是一个选择信息的过程.我不能发布查询本身，但我可以发布围绕该过程的语法:

CREATE PROCEDURE BusinessInfo@变量 VarChar(10)作为开始选择信息.*从表信息WHERE Info.PersonKey = @VariableORDER BY Info.LastName结尾走EXECUTE BusinessInfo '1'走

问题是无论我做什么，只要我放置了创建过程，它就什么都不返回.我什至构建了程序，说 END GO 并在之后重新编写整个程序查询，它什么也没有拉回来，然后我删除了程序，它拉回了我正在寻找的信息.我做错了什么?

如果您需要一个工作示例，这将适用于 SQLFiddle 中的任何架构

创建程序示例作为开始选择信息"结尾走执行示例走

解决方案

可能的解决方案:

1) 改成这样(默认终止符是分号):

选择 GO 作为终止符后的代码

CREATE PROCEDURE BusinessInfo@变量 VarChar(10)作为开始选择信息*从表信息WHERE Info.PersonKey = @VariableORDER BY Info.LastName结尾走EXECUTE BusinessInfo '1'走

基于文档

<块引用>

那个 [ ;] 按钮?

这个不起眼的小按钮决定了每个面板中的查询在发送到数据库之前如何分解.此按钮会弹出一个下拉列表，其中列出了不同的查询终止符".查询终止符用作标志以指示(当出现在行尾时)当前语句已结束.终止符不会被发送到数据库；相反，它只是指出在执行查询之前我应该​​如何解析文本.

通常，您不需要触摸此按钮；此功能的主要价值在于定义存储过程.这是因为通常情况下，在存储过程的主体定义中，您可能希望以分号结束一行(通常是这种情况).由于我的默认查询终止符也是分号，因此我没有明显的方法可以看出您的存储过程的分号实际上不是查询的结尾.留下分号终止符，我会将您的过程定义分解为不正确的部分，并且肯定会导致错误.将查询终止符更改为分号以外的内容可避免此问题.

I'm building a query in SQLFiddle using SQL Server 2008. This query is a procedure that selects information. I can't post the query itself, but I can post the Syntax surrounding the procedure:

CREATE PROCEDURE BusinessInfo
  @Variable VarChar(10)
AS
BEGIN
  SELECT   Info.*
  FROM     Table Info
  WHERE    Info.PersonKey = @Variable
  ORDER BY Info.LastName
END
GO
EXECUTE BusinessInfo '1'
GO

The problem is that no matter what I do, as soon as I put create procedure, it returns nothing. I even built the Procedure, said END GO and re-wrote the entire procedure query afterwards and it pulled back nothing, and then I deleted the Procedure and it pulled back the information I was looking for. What am I doing wrong?

If you need a working example, this will work on any Schema in SQLFiddle

CREATE PROCEDURE Sample
AS
BEGIN
SELECT   'Information'
END
GO
EXECUTE Sample
GO

解决方案

Possible solutions:

1) Change to this (default terminator is semicolon): SqlFiddleDemo

CREATE PROCEDURE Sample
AS
BEGIN
    SELECT   'Information'
END;

EXECUTE Sample

2) Change query terminator using 4th button to GO and your example will work.

Your code after selecting GO as terminator

CREATE PROCEDURE BusinessInfo
  @Variable VarChar(10)
AS
BEGIN
  SELECT   Info.*
  FROM     Table Info
  WHERE    Info.PersonKey = @Variable
  ORDER BY Info.LastName
END
GO

EXECUTE BusinessInfo '1'
GO

Based on documentation

What's up with that [ ; ] button under each panel?

This obscure little button determines how the queries in each of the panels get broken up before they are sent off to the database. This button pops open a dropdown that lists different "query terminators." Query terminators are used as a flag to indicate (when present at the end of a line) that the current statement has ended. The terminator does not get sent to the database; instead, it merely idicates how I should parse the text before I execute the query.

Oftentimes, you won't need to touch this button; the main value this feature will have is in defining stored procedures. This is because it is often the case that within a stored procedure's body definition, you might want to end a line with a semicolon (this is often the case). Since my default query terminator is also a semicolon, there is no obvious way for me to see that your stored procedure's semicolon isn't actually the end of the query. Left with the semicolon terminator, I would break up your procedure definition into incorrect parts, and errors would certainly result. Changing the query terminator to something other than a semicolon avoids this problem.

这篇关于在 SQLFiddle 中使用过程访问时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！