问题描述:

I have table called my_table with this schema:

+----+--------------------------+-----------------------------+--------------------+

| ID | name (4 possible values) | action (2 possible values) | sequence (1,2...n) |

+----+--------------------------+-----------------------------+--------------------+

I want to create a stored procedure that can process the data according to the sequence from the sequence column. This stored procedure should look at the value in sequence then execute the corresponding stored procedure. Current I have 8 separate stored procedures.

For example, if seq = 1 then stored procedure #1 should be executed.

If seq = 2 it goes to stored procedure #4 depending on the values of name and action. There are 8 possible matches.

Here's my attempt:

SET NOCOUNT ON

DECLARE @name varchar(20), @action varchar(20), @ID int, @Seq varchar(20)

DECLARE ILOOP CURSOR FOR SELECT ID, Name, Action, Seq FROM my_table

OPEN ILOOP

FETCH NEXT FROM iloop INTO @ID, @name, @Action, @Seq

WHILE @@Fetch_Status = 0 BEGIN

SELECT * From my_table WHERE Name = case Name when 'provider'

then EXEC sp1

END

CLOSE ILOOP

DEALLOCATE ILOOP

RETURN

网友答案:

You have neglected to tell us anything about the specific logic used to select the stored procedures or what parameters they take but it seems you need something along these lines.

SET NOCOUNT ON

DECLARE @Name VARCHAR(20) ,
    @Action VARCHAR(20) ,
    @ID INT ,
    @Seq VARCHAR(20)

DECLARE ILOOP CURSOR
FOR
    SELECT  ID ,
            Name ,
            Action ,
            Seq
    FROM    my_table
    ORDER BY Seq

OPEN ILOOP
FETCH NEXT FROM ILOOP INTO @ID, @Name, @Action, @Seq
WHILE @@Fetch_Status = 0 
    BEGIN
        IF @Name = 'A'
            AND @Action = 'X' 
            EXEC sp1 @Action, @ID, @Name
        ELSE 
            IF @Name = 'C'
                AND @Action = 'Y' 
                EXEC sp2 @Action, @ID, @Name

        FETCH NEXT FROM ILOOP INTO @ID, @Name, @Action, @Seq    
    END
CLOSE ILOOP
DEALLOCATE ILOOP 
相关阅读:
Top