Trouble accessing with Procedure in SQLFiddle

2019-07-16 04:22发布

问题:

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

回答1:

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.