带有 if else sql server 2008 的存储过程

stored procedure with if else sql server 2008

提问by jhon smith

I have 3 tables, I have to check if grandparent table id has records in grandchildren table. If yes, return yes, else return no. Here is my stored procedure. I got an incorrect syntax error. I am new to stored procedure. Please help me.

我有 3 个表，我必须检查祖父表 id 是否在孙表中有记录。如果是，返回是，否则返回否。这是我的存储过程。我收到了不正确的语法错误。我是存储过程的新手。请帮我。

CREATE PROCEDURE P_Check
   @PKG_ID INT,
   @S_ID INT,
   @FLAG VCHAR(10) OUT

   DECLARE IDS CURSOR LOCAL FOR SELECT S_ID FROM T1 WHERE P_ID = @PKG_ID
   OPEN IDS
   FETCH NEXT FROM IDS into @S_ID

   WHILE @@FETCH_STATUS = 0
   BEGIN
      SELECT * FROM T2 WHERE S_ID = @S_ID

      IF @@ROWCOUNT<>0
         @FLAG = 'YES'
         RETURN 
      ELSE
         @FLAG = 'NO'

      FETCH NEXT FROM IDS into @S_ID
   END

   CLOSE IDS
   DEALLOCATE IDS

回答by von v.

A few things to check:

需要检查的几件事：

1. I don't think there is a vchardata type in SQL Server unless that is your custom type. So change it to varchar
2. You forgot the AS
3. You might want to enclose your logic inside ifin between beginand end

1. 我认为vcharSQL Server 中没有数据类型，除非这是您的自定义类型。所以改成varchar
2. 你忘记了 AS
3. 您可能希望将您的逻辑包含if在begin和之间end

Your code that can be compiled:

您可以编译的代码：

CREATE PROCEDURE P_Check
    @PKG_ID INT,
    @S_ID INT,
    @FLAG VARCHAR(10) OUT
AS
    DECLARE IDS CURSOR LOCAL FOR SELECT S_ID FROM T1 WHERE P_ID = @PKG_ID
    OPEN IDS
    FETCH NEXT FROM IDS into @S_ID
    WHILE @@FETCH_STATUS = 0

    BEGIN
      SELECT * FROM T2 WHERE S_ID = @S_ID
      IF @@ROWCOUNT<>0
      BEGIN
         SET @FLAG = 'YES'
         RETURN 
      END
      ELSE
      BEGIN
         SET @FLAG = 'NO'
         FETCH NEXT FROM IDS into @S_ID
      END
    END

    CLOSE IDS
    DEALLOCATE IDS

However, I think your cursor will not be closed as you are returning here IF @@ROWCOUNT<>0. I think what you should do is change this:

但是，我认为当您返回此处时，您的光标不会关闭IF @@ROWCOUNT<>0。我认为你应该做的是改变这一点：

IF @@ROWCOUNT<>0
BEGIN
    SET @FLAG = 'YES'
    RETURN 
END

to this:

对此：

IF @@ROWCOUNT<>0
BEGIN
    SET @FLAG = 'YES'
    GOTO ON_EXIT
END

then end your procedure like this:

然后像这样结束你的程序：

ON_EXIT:
    CLOSE IDS
    DEALLOCATE IDS    

Then to answer your question in the comment, you are "returning" it already in a sense. You can call and test your procedure like this:

然后在评论中回答您的问题，从某种意义上说，您已经“返回”了它。您可以像这样调用和测试您的程序：

declare @result varchar(10)
exec P_Check 1, 1, @result out
print @result

回答by marc_s

This is just way too complicated and using a cursor here is totally not needed and totally unnecessary.

这太复杂了，在这里使用游标完全没有必要，也完全没有必要。

Simplify your procedure to be:

将您的程序简化为：

CREATE PROCEDURE P_Check
   @PKG_ID INT,
   @S_ID INT,
   @FLAG CHAR(1) OUT
AS BEGIN
   IF EXISTS (SELECT * FROM T2 
              INNER JOIN T1 ON T2.S_ID = T1.S_ID WHERE P_ID = @PKG_ID)
      SET @FLAG = 'Y'
   ELSE
      SET @FLAG = 'N'
END

When working seriously with SQL Server, you need to get away from the procedural row-by-agonizing-rowthinking using cursors and loops, and you need to start thinking in setsto be efficient and productive.

在认真使用 SQL Server 时，您需要摆脱使用游标和循环的逐行逐行思考的过程，并且您需要开始成组思考以提高效率和生产力。