Adding Column to stored procedure conditionally

Question

I have the following (simplified) stored procedure:

    CREATE FUNCTION [dbo].[UDF_FulfilmentBatch](@FulfilmentID INT) RETURNS
    @Result TABLE (
            [sequence]  INT,
            membershipid    BIGINT,
            membershipNo    VARCHAR(255)
    )
    AS
    BEGIN

    DECLARE @_sequence  INT
    DECLARE @_membershipid  BIGINT
    DECLARE @_membershipNo  VARCHAR(255)

    SET @_sequence = 1

    IF @FulfilmentID = 4
    BEGIN
        DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
            SELECT * from VW_FulfilmentExtract_HH
    END
IF @FulfilmentID = 3
    BEGIN
        DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
            SELECT * from VW_FulfilmentExtract_ID
    END
    ELSE IF @FulfilmentID = 2
    BEGIN
        DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
            SELECT * from VW_FulfilmentExtract_Art
    END
    ELSE
        DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
            SELECT * from VW_FulfilmentExtract_Tha

    OPEN FulfilCursor

    FETCH NEXT FROM FulfilCursor INTO @_membershipid, @_membershipNo

    WHILE @@FETCH_STATUS = 0 BEGIN

        INSERT INTO @Result
        VALUES (@_sequence, @_membershipid, @_membershipNo) 

        SET @_sequence = @_sequence + 1 

        FETCH NEXT FROM FulfilCursor INTO @_membershipid, @_membershipNo
    END

    CLOSE FulfilCursor
    DEALLOCATE FulfilCursor

    RETURN

    END




    GO

My problem is, that when FulfilmentID = 4, I wan to add an extra field - 'Delivery'

If have tried the following:

CREATE FUNCTION [dbo].[UDF_FulfilmentBatch](@FulfilmentID INT) RETURNS
        @Result TABLE (
                [sequence]  INT,
                membershipid    BIGINT,
                membershipNo    VARCHAR(255)
        IF @FulfilmentID = 4
        BEGIN
        ,Delivery   VARCHAR(255)
        END
        )
        AS
        BEGIN

        DECLARE @_sequence  INT
        DECLARE @_membershipid  BIGINT
        DECLARE @_membershipNo  VARCHAR(255)
IF @FulfilmentID = 4
        BEGIN
        DECLARE @_Delivery  VARCHAR(255)
        END

        SET @_sequence = 1

        IF @FulfilmentID = 4
        BEGIN
            DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
                SELECT * from VW_FulfilmentExtract_HH
        END
    IF @FulfilmentID = 3
        BEGIN
            DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
                SELECT * from VW_FulfilmentExtract_ID
        END
        ELSE IF @FulfilmentID = 2
        BEGIN
            DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
                SELECT * from VW_FulfilmentExtract_Art
        END
        ELSE
            DECLARE FulfilCursor CURSOR FAST_FORWARD FOR 
                SELECT * from VW_FulfilmentExtract_Tha

        OPEN FulfilCursor

        FETCH NEXT FROM FulfilCursor INTO @_membershipid, @_membershipNo

        WHILE @@FETCH_STATUS = 0 BEGIN

            INSERT INTO @Result
            VALUES (@_sequence, @_membershipid, @_membershipNo IF @FulfilmentID=4 BEGIN @_Delivery  )   

            SET @_sequence = @_sequence + 1 

            FETCH NEXT FROM FulfilCursor INTO @_membershipid, @_membershipNo
        END

        CLOSE FulfilCursor
        DEALLOCATE FulfilCursor

        RETURN

        END




        GO

But this did not work (Please excuse any syntax errors, this is a rough typing for SO)

Form searching the web there does not seem to be much on this. Can it be done?

Answer 1

There are lots of issues in your code:

• use SELECT *
• use Cursor
• ...

In the end, it is overly complicated and looks at lot like Application code (C#, java, ...)

What you want to do should be done with a single Select (ie. on a set of data). Columns names should also be listed between SELECT and FROM.

One option is:

CREATE FUNCTION [dbo].[UDF_FulfilmentBatch](@FulfilmentID INT) RETURNS TABLE
AS
RETURN (
    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery
    FROM VW_FulfilmentExtract_HH
    WHERE @FulfilmentID = 4

    UNION ALL

    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery = NULL
    FROM VW_FulfilmentExtract_ID
    WHERE @FulfilmentID = 3

    UNION ALL

    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery = NULL
    FROM VW_FulfilmentExtract_Art
    WHERE @FulfilmentID = 2

    UNION ALL

    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery = NULL
    FROM VW_FulfilmentExtract_Tha
    WHERE @FulfilmentID = not in (2, 3, 4)
);

Here I use ROW_NUMBER to generate your sequence number.

I added the Delivery column and set it to NULL when it is not needed.

You must update columns name. I just guess them.

By the way, this is not a Stored Procedure but a Inline User-Defined Functions