Reputation: 3998
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?
Upvotes: 1
Views: 225
Reputation: 3962
There are lots of issues in your code:
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
Upvotes: 2