Using cursor within UDF, returns a column which has name of different table

Question

Create FUNCTION [dbo].[fn_GetStockDeatils]()
RETURNS @Results TABLE
(
PurchaseData nvarchar(50) NOT NULL 

)
AS
    BEGIN


    Declare @tableName varchar(25)
    Declare @PKKey numeric(18,0)

    DECLARE StockCursor CURSOR FOR Select tableName ,PKKey from INVM 
    OPEN StockCursor
    FETCH StockCursor INTO @tableName ,@PKKey 
       Begin

         Insert @Results Select PurchaseData from @tableName.PKKey =@PKKey 

       END      
    FETCH StockCursor INTO @tableName ,@PKKey 
    close StockCursor
    DEALLOCATE StockCursor


        Return  

    END

I have already written this but it does not working properly. @tableName contains the name of the table. Please help me if possible.

Answer 1

Sounds like you need an dynamic SQL commands, but you cannot use dynamic SQL from a function. For that you have to create stored procedure.

CREATE PROCEDURE [dbo].[GetStockDeatils]
AS
BEGIN
DECLARE @tableName varchar(25),
        @PKKey numeric(18,0),
        @dsql nvarchar(max) = N''

IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE dbo.#Results
CREATE TABLE dbo.#Results(PurchaseData nvarchar(50) NOT NULL)

DECLARE StockCursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT tableName ,PKKey 
FROM INVM 
OPEN StockCursor
FETCH NEXT FROM StockCursor INTO @tableName, @PKKey
WHILE @@FETCH_STATUS = 0
BEGIN 
  SET @dsql = 'Insert dbo.#Results Select PurchaseData from ' + @tableName + ' WHERE PKKey = ' + CAST(@PKKey AS varchar(10))
  EXEC sp_executesql @dsql  
  FETCH NEXT FROM StockCursor INTO @tableName, @PKKey 
END
CLOSE StockCursor
DEALLOCATE StockCursor

SELECT *
FROM dbo.#Results
END

EXEC [dbo].[GetStockDeatils]

Demo on SQLFiddle

Procedure creating view

CREATE PROCEDURE [dbo].[GetStockDeatils]
AS
BEGIN
DECLARE @dsql nvarchar(max) = N''
IF OBJECT_ID('dbo.v_Results') IS NOT NULL DROP VIEW dbo.v_Results
SELECT @dsql += 
  'UNION ALL SELECT PurchaseData FROM ' + tableName + 
  ' WHERE PKKey = ' + CAST(PKKey AS varchar(10))                
FROM dbo.INVM
SET @dsql = N'CREATE VIEW dbo.v_Results AS ' + STUFF(@dsql, 1, 10, '')
EXEC sp_executesql @dsql
END

EXEC [dbo].[GetStockDeatils]

SELECT *
FROM dbo.v_Results