问存储过程临时表适用于不同的用户和生命周期

EN

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test]
    @Level INT,
    @KatSubkatId INT,   
    @WordId INT,
    @SubsubkatId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @temp_T_Html_Word_Categories TABLE(Id INT)
    DECLARE @temp_T_Html_WordCat_Phrase TABLE(FK_Phrase_ID INT)

    IF (@Level = 2)
        BEGIN
            INSERT INTO @temp_T_Html_Word_Categories(Id) SELECT Id FROM T_Html_Word_Categories WHERE FK_KatSubkat_ID = @KatSubkatId And FK_Word_ID = @WordId
        END
    ELSE IF (@Level = 3)
        BEGIN
            INSERT INTO @temp_T_Html_Word_Categories(Id) SELECT Id FROM T_Html_Word_Categories WHERE FK_KatSubkat_ID = @KatSubkatId And FK_Word_ID = @WordId And FK_Subsubkat_ID IS NULL;
            INSERT INTO @temp_T_Html_Word_Categories(Id) SELECT Id FROM T_Html_Word_Categories WHERE FK_KatSubkat_ID = @KatSubkatId And FK_Word_ID = @WordId And FK_Subsubkat_ID = @SubsubkatId;
        END
    ELSE
        BEGIN
            SELECT null;
            RETURN;
        END

        print 'I am here'

    INSERT INTO @temp_T_Html_Word_Categories(Id) SELECT Id FROM T_Html_Word_Categories WHERE FK_KatSubkat_ID = @KatSubkatId And FK_Word_ID = @WordId

    --DECLARE @v XML = (SELECT * FROM @temp_T_Html_Word_Categories FOR XML AUTO)

    Declare @Id int;
    --it makes no difference what you SELECT in an EXISTS sub-query as it is just syntactical sugar. (ie Nothing is actually selected.) SELECT * or SELECT 1 ...
    WHILE EXISTS(SELECT * FROM @temp_T_Html_Word_Categories)        
    BEGIN
        Select Top 1 @Id = Id From @temp_T_Html_Word_Categories;

    INSERT INTO @temp_T_Html_WordCat_Phrase(FK_Phrase_ID) SELECT FK_Phrase_ID FROM T_Html_WordCat_Phrase WHERE FK_Word_Categorie_ID = @Id;
        Delete @temp_T_Html_Word_Categories Where Id = @Id;     
    END;

    --IF EXISTS (SELECT * FROM T_Html_Phrase WHERE Id NOT IN (SELECT FK_Phrase_ID FROM @temp_T_Html_WordCat_Phrase))
    --  SET @IddD = 1;
    --ELSE
    --  SET @IddD = 0;


    SELECT * FROM T_Html_Phrase WHERE Id NOT IN (SELECT FK_Phrase_ID FROM @temp_T_Html_WordCat_Phrase)

    --DECLARE @b XML = (SELECT * FROM T_Html_Phrase FOR XML AUTO)

    Delete @temp_T_Html_WordCat_Phrase;

END