This function I have created to return all recorded Stock numbers in the Table "ContributorDeeds" the table contains only the start number of the stocks and the end number of the stocks (e.g, from number - to number) I need to get all the numbers between within that range.
The function works fine, however, it tasks several minutes to return the result :(.
Is there a better SQL Query to get the same result?
FUNCTION [dbo].[StockNumbers] () RETURNS @SequenceNumbersOfShares TABLE (StockNumber NVARCHAR(50))
AS
BEGIN
DECLARE @DeedDocumentID BIGINT DECLARE @MIN NVARCHAR(50) DECLARE @MAX NVARCHAR(50)
--CREATE TABLE #LocalTempTable(StockNumber NVARCHAR(50))
DECLARE db_cursor CURSOR FOR SELECT ContributorDeedsID FROM dbo.ContributorDeeds WHERE DeedFromShare IS NOT NULL AND DeedToShare IS NOT NULL
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DeedDocumentID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MIN = (SELECT DeedFromShare FROM ContributorDeeds WHERE ContributorDeedsID = @DeedDocumentID)
SET @MAX = (SELECT DeedToShare FROM ContributorDeeds WHERE ContributorDeedsID = @DeedDocumentID)
WHILE dbo.InsertSP(@MIN, 20) <= dbo.InsertSP(@MAX, 20)
BEGIN
INSERT INTO @SequenceNumbersOfShares(StockNumber) VALUES(@MIN)
SET @MIN = @MIN + 1
END
FETCH NEXT FROM db_cursor INTO @DeedDocumentID
END
CLOSE db_cursor
DEALLOCATE db_cursor
RETURN;
END;