创建存储过程

Question

在SQL Server中创建存储过程时，我面临问题；是否有什么我遗漏了/做错了什么？

我添加了查询来创建数据库和创建过程，非常感谢您的帮助和指导。

如果你需要更多的细节，请告诉我！

存储过程(麻烦)

USE [ChartSample]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  

ALTER PROCEDURE [dbo].[SC_GetPlayers]  
AS  
BEGIN  
    SELECT  *  
    FROM CS_Player   
END  

USE [ChartSample]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  

ALTER PROCEDURE [dbo].[SC_GetPlayerRecordsBtPlayerId] 
    @PlayerId INT  
AS  
BEGIN  
    SELECT  
        PlayerId, 
        Year,  
        TotalRun, TotalWickets,  
        ODIMatches, TestMatches  
    FROM    
        CS_PlayerRecord  
    WHERE   
        PlayerId = @PlayerId  
END 

错误消息：

Msg 154，15级，状态1，过程SC_GetPlayers，第8行批处理开始行7 在过程、函数或触发器中不允许使用数据库语句。 Msg 208，16级，状态6，过程SC_GetPlayerRecordsBtPlayerId，第一行批处理起始线21 无效的对象名'dbo.SC_GetPlayerRecordsBtPlayerId‘。

创建小型数据库的查询：

CREATE DATABASE ChartSample  

USE [ChartSample]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
SET ANSI_PADDING ON  
GO  

CREATE TABLE [dbo].[CS_Player]
(  
    [PlayerId] [int] IDENTITY(1,1) NOT NULL,  
    [PlayerName] [varchar](50) NULL,  

    CONSTRAINT [PK_CS_Player] 
        PRIMARY KEY CLUSTERED ([PlayerId] ASC)
) ON [PRIMARY]  
GO  

SET ANSI_PADDING OFF  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  

CREATE TABLE [dbo].[CS_PlayerRecord]
(  
    [ID] [int] IDENTITY(1,1) NOT NULL,  
    [PlayerId] [int] NULL,  
    [Year] [int] NULL,  
    [TotalRun] [int] NULL,  
    [TotalWickets] [int] NULL,  
    [ODIMatches] [int] NULL,  
    [TestMatches] [int] NULL,  

    CONSTRAINT [PK_CS_PlayerRecord] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]  
GO  

ALTER TABLE [dbo].[CS_layerRecord]  WITH CHECK 
    ADD CONSTRAINT [FK_CS_PlayerRecord_CS_Player] 
        FOREIGN KEY([PlayerId]) REFERENCES [dbo].[CS_Player]([PlayerId])  
GO  

ALTER TABLE [dbo].[CS_PlayerRecord] CHECK CONSTRAINT [FK_CS_PlayerRecord_CS_Player]  
GO 

Answer 1

尝试在两个proc之间使用GO，否则它将被视为SC_GetPlayers proc代码的一部分。使用GO将帮助编译器理解它是批处理的结束。

编辑:似乎您正在尝试创建procs，所以使用Create而不是Alter

Create PROC [dbo].[SC_GetPlayers]  
AS  
    BEGIN  
        SELECT  *  
        FROM    CS_Player   
    END  

GO--here

USE [ChartSample]  
GO  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
Create PROC [dbo].[SC_GetPlayerRecordsBtPlayerId] @PlayerId INT  
..

Answer 2

在第二个GO语句之前添加一个USE。

另外，作为附带说明，您可能需要拼写第二个存储过程：GetPlayerRecordsByPlayerID。