这里的存储过程定义有什么问题？

I have like 20 errors but I'll list 3 and maybe you can help me understand what I'm doing wrong overall with trying to write stored procedures based on the documentation https://msdn.microsoft.com/en-us/library/ms345415.aspx.

我有20个错误，但我会列出3个，也许你可以帮我理解我在整个尝试编写基于文档的存储过程时所做的错误https://msdn.microsoft.com/en-us/library /ms345415.aspx。

Full code:

完整代码：

CREATE DATABASE JsPracticeDb; 
/* Create tables corresponding to the problems, solutions to 
   problems, and ratings of problems or solutions */
GO

USE[JsPracticeDb]
Go

/* Table representing JavaScript problems. The promp_code 
   is the HTML that formats the JS code for the view. */
CREATE TABLE Problems 
( 
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
    prompt_code VARCHAR(5000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);

/* Create sprocs for adding and deleting problems */
GO
CREATE PROC AddProblem  
    @prompt_code VARCHAR(5000) 
AS 
    INSERT INTO Problems (@prompt_code)
GO

CREATE PROC DeleteProblem
    @id INT
AS
    DELETE FROM Problems WHERE id=@id
GO  

/* Table representing JavaScript solutions (in formatted HTML),
   associated solvers and code that tests validity of solutions */
CREATE TABLE Solutions 
(
   id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
   problem_id INT NOT NULL,
   solver VARCHAR(50),
   solution_code VARCHAR(5000),
   test_code VARCHAR(8000),
   FOREIGN KEY (problem_id) REFERENCES Problems(id) ON DELETE CASCADE,
   created DATETIME DEFAULT CURRENT_TIMESTAMP
);

/* Create PROCEDURE for adding and deleting solutions */
GO
CREATE PROC AddSolution
    @problem_id INT,
    @solver VARCHAR(50),
    @solution_code VARCHAR(5000),
    @test_code VARCHAR(8000)
AS  
    INSERT INTO Solutions (@problem_id, @solver, @solution_code, @test_code)
GO

CREATE PROC DeleteSolution 
    @id INT 
AS
    DELETE FROM Solutions WHERE id=@id

/* Table representing 0-5 star rating of associated solutions */
CREATE TABLE Ratings 
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    stars TINYINT NOT NULL,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
/* Create sproc for adding ratings */
GO

CREATE PROCEDURE AddRating
    @solution_id INT,
    @stars TINYINT 
AS
    INSERT Into Ratings (@solution_id, @stars)
GO

/* Table representing comments on solutions or comments on coments, and
   the associated commenter. The association of comments on comments is 
   mapped in the next table, CommentPaths   */
CREATE TABLE Comments 
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    commenter VARCHAR(50),
    cmnt VARCHAR(2000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);

/* Create sprocs for adding and deleting comments 
CREATE PROCEDURE AddComment
    @solution_id INT NOT NULL,
    @commenter VARCHAR(50),
    @cmnt VARCHAR(2000) NOT NULL,
    @parent_id \
AS 
    INSERT INTO Comments (@solution_id, @commenter, @cmnt)
   Still implementing   
*/

CREATE PROCEDURE DeleteComment 
    @id
AS 
    DELETE FROM Comments WHERE id=@id
GO

/* Closure Table for comment tree, e.g.

                         Comments
    ==================================================
    id | solution_id | commenter | comment | created
    --------------------------------------------------
    1  |    1        | ......... | .....   | ......
    2  |    1        | ......... | .....   | ......
    3  |    1        | ......... | .....   | ......
    4  |    1        | ......... | .....   | ......    
    5  |    2        | ......... | .....   | ......    
    6  |    2        | ......... | .....   | ...... 


                      CommentPaths 
                ========================
                 ancestor | descendant
                ------------------------
                    1     |     2
                    1     |     3 
                    1     |     4
                    2     |     4
                    5     |     6

corresponds to the Comments ids being related
to each other like

                1       5
               / \      |
              2   3     6
             /
            4

 */
CREATE TABLE CommentPaths 
(
    ancestor_id INT NOT NULL,
    descendant_id INT NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id),
    FOREIGN KEY (ancestor_id) REFERENCES Comments(id) ON CASCADE DELETE,
    FOREIGN KEY (descendant_id) REFERENCES Comments(id)
);

/* sproc called on delete of a comment to delete descendant comments
   The references to the descendant comments in CommentPaths */
GO
CREATE PROC DeleteCommentDescendens  
    @AncestorId INT 
AS 
    /* http://*.com/questions/506602/best-way-to-work-with-transactions-in-ms-sql-server-management-studio */
    BEGIN TRY
        SELECT descendant_id FROM CommentPaths WHERE ancestor_id=@AncestorId AS descs
        DELETE FROM Comments WHERE id IN descs
        DELETE FROM CommentPaths WHERE ancestor_id IN descs OR descendant_id IN descs
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

The first 3 errors are

前三个错误是

Msg 102, Level 15, State 1, Procedure AddProblem, Line 20
Incorrect syntax near ')'.

消息102，级别15，状态1，过程AddProblem，第20行'）'附近的语法不正确。

Msg 102, Level 15, State 1, Procedure AddSolution, Line 46
Incorrect syntax near ')'.

消息102，级别15，状态1，过程AddSolution，第46行'）'附近的语法不正确。

Msg 102, Level 15, State 1, Procedure AddRating, Line 66
Incorrect syntax near ')'.

消息102，级别15，状态1，过程AddRating，第66行'）'附近的语法不正确。

which refer to the lines

这是指线条

INSERT INTO Problems (@prompt_code)

and

和

INSERT INTO Solutions (@problem_id, @solver, @solution_code, @test_code)

and

和

INSERT Into Ratings (@solution_id, @stars)

respectively.

分别。

2 个解决方案

#1

You have a number of syntax errors:

您有许多语法错误：

In AddProblem:

在AddProblem中：

INSERT INTO Problems (@prompt_code)

should be:

应该：

INSERT INTO Problems (prompt_code) VALUES(@prompt_code)

---

In AddSolution

在AddSolution中

INSERT INTO Solutions (@problem_id, @solver, @solution_code, @test_code)

should be:

应该：

INSERT INTO Soulutions (problem_id, solver, solution_code, test_code) VALUES (@problem_id, @solver, @solution_code, @test_code)

---

In AddRating

在AddRating中

INSERT INTO Ratings (@solution_id, @stars)

should be

应该

INSERT INTO Ratings (solution_id, stars) VALUES (@solution_id, @stars)

---

In DeleteComment

在DeleteComment中

CREATE PROCEDURE DeleteComment
    @id

should be

应该

CREATE PROCEDURE DeleteComment
    @id INT

---

In CommentPaths table creation:

在CommentPaths表创建中：

FOREIGN KEY (ancestor_id) REFERENCES Comments(id) ON CASCADE DELETE

should be

应该

FOREIGN KEY (ancestor_id) REFERENCES Comments(id) ON DELETE CASCADE

---

In DeleteCommentDescendens

在DeleteCommentDescendens中

SELECT descendant_id FROM CommentPaths WHERE ancestor_id=@AncestorId AS descs
DELETE FROM Comments WHERE id IN descs
DELETE FROM CommentPaths WHERE ancestor_id IN descs OR descendant_id IN descs

should be:

应该：

DECLARE @descs AS TABLE(descendant_id INT)

INSERT INTO @descs(descendant_id)
    SELECT descendant_id FROM CommentPaths WHERE ancestor_id=@AncestorId

DELETE FROM Comments WHERE id IN (SELECT descendant_id FROM @descs)
DELETE FROM CommentPaths WHERE ancestor_id IN (SELECT descendant_id FROM @descs) OR descendant_id IN (SELECT descendant_id FROM @descs)

#2

Are you new to T-Sql Programming? If that's the case I would suggest you to go through documentation or learning material to go about the syntax.

你是T-Sql编程的新手吗？如果是这种情况，我建议您通过文档或学习材料来了解语法。

The problem in your case is you need to mention the list of the columns or match the columns that values should be inserted for. Further you run into a problem if you add a new column to the table then what will you do? It might work for you, but if somebody else needs to maintain and enhance the code it will not make an iota of sense to him/her.

在您的情况下，您需要提及列的列表或匹配应插入值的列。如果你向表中添加一个新列，那么你会遇到什么问题然后你会做什么？它可能适合你，但如果其他人需要维护和增强代码，它将不会对他/她产生任何意义。

So I will suggest that you explicitly mention the column names barring the identity columns and the defaults that you have e.g:

因此，我建议您明确提及禁止标识列的列名称以及您拥有的默认值：

    CREATE PROC AddProblem  
       @prompt_code VARCHAR(5000) 
    AS 
       INSERT INTO Problems(prompt_code) values(@prompt_code)
    GO