CODEWITHSUNDEEP
sqlsql-server
carl Brooks
carl Brooks

Reputation: 315

Create view error as statement must be only one in the batch

This is the first time I'm trying to create a view in t-sql but I'm receiving an error stating: CREATE VIEW must be the only statement in the batch.

Why is it giving me this error and how can it be fixed?

I looked at some examples but still quite not sure on what I need to do.

CREATE PROCEDURE [dbo].[League_Table_Insert]
    @LeagueName VARCHAR(30)
AS
SET NOCOUNT ON
BEGIN

    CREATE VIEW League_Table AS  
    SELECT
        TeamName AS Team, 
        COUNT(*) Played, 
        COUNT(CASE WHEN HomeScore > AwayScore THEN 1 END) Wins, 
        COUNT(CASE WHEN AwayScore > HomeScore THEN 1 END) Losses, 
        COUNT(CASE WHEN HomeScore = AwayScore THEN 1 END) Draws, 
        SUM(HomeScore) Goals_Scored, 
        SUM(AwayScore) Goals_Against, 
        SUM(HomeScore) - SUM(AwayScore) Goal_Difference,
        SUM(
              CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END 
            + CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END
        ) Points

    FROM
    (
        SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
        UNION ALL
        SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
    ) matches

    GROUP BY TeamName
    ORDER BY Points DESC, Goal_Difference DESC;

Upvotes: 0

Views: 439

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28938

MSDN documentation clearly states below:

The following statements cannot be used anywhere in the body of a stored procedure

CREATE AGGREGATE
CREATE SCHEMA
SET SHOWPLAN_TEXT
CREATE DEFAULT
CREATE or ALTER TRIGGER
SET SHOWPLAN_XML
CREATE or ALTER FUNCTION
CREATE or ALTER VIEW
USE database_name
CREATE or ALTER PROCEDURE
SET PARSEONLY
CREATE RULE
SET SHOWPLAN_ALL

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You cannot create a VIEW inside a function. Also, you cannot use a variable inside a VIEW. What you want is a table-valued function. Here is a create script for an Inline Table-Valued Function (iTVF):

CREATE FUNCTION dbo.League_Table (
    @LeagueName     VARCHAR(MAX)
) 
RETURNS TABLE
AS
RETURN

SELECT
    TeamName AS Team, 
    COUNT(*) Played, 
    COUNT(CASE WHEN HomeScore > AwayScore THEN 1 END) Wins, 
    COUNT(CASE WHEN AwayScore > HomeScore THEN 1 END) Losses, 
    COUNT(CASE WHEN HomeScore = AwayScore THEN 1 END) Draws, 
    SUM(HomeScore) Goals_Scored, 
    SUM(AwayScore) Goals_Against, 
    SUM(HomeScore) - SUM(AwayScore) Goal_Difference,
    SUM(
          CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END 
        + CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END
    ) Points

FROM
(
    SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
    UNION ALL
    SELECT TeamName, HomeScore, AwayScore FROM dbo.Team t INNER JOIN dbo.Fixture f ON t.TeamID = f.HomeTeamID INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID WHERE LeagueName = @LeagueName
) matches

GROUP BY TeamName;

Note that I removed the ORDER BY clause because it's not allowed in inline functions. Otherwise, you'll get an error stating:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Then, to use it:

SELECT * 
FROM dbo.League_Table('Sample League Name') s
ORDER BY Points DESC, Goal_Difference DESC;

Upvotes: 3

Related Questions