title: How to call the Stored Procedure in the Table Valued Function in SQL Server 2005?
body: <p>I want to call the Stored Procedure which returns a table in the Table Valued Function. How to create the Table Valued Function?</p>
  
  <p>For Example:  "sp_GetTable" is the Stored procedure. It returns a table.
  Here, I want to write one function called "fn_GetTable"</p>
  
  <p>I want to get the same result which has given by Stored Procedure.</p>
  
  <p>Here is my Stored Procedure:</p>
  
  <pre><code>ALTER PROC sp_GetTable
  AS
  BEGIN
  
  ------------ Creating TempTable(#MasterTable) ----------------------
      CREATE TABLE #MasterTable
      (ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50),   D2 VARCHAR(50), D3 VARCHAR(50), 
          D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50))
  
  ------------ Creating TempTable(#TempItems) ----------------------
  
      CREATE TABLE #TempItems(ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50),
                   D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50))
  ------------ Creating &amp; Inserting TempTable(@Dim) ----------------------
  
      DECLARE @Dim TABLE (DCodes VARCHAR(100))
      INSERT INTO @Dim SELECT  D1_Code FROM MAS_SizeType WHERE  D1_Code &lt;&gt; ''
      INSERT INTO @Dim SELECT  D2_Code FROM MAS_SizeType WHERE  D2_Code &lt;&gt; ''
      INSERT INTO @Dim SELECT  D3_Code FROM MAS_SizeType WHERE  D3_Code &lt;&gt; ''
  
  ------------ Inserting data into TempTable(#MasterTable) ----------------------
  
      INSERT INTO #MasterTable
      SELECT     STR_Item.ItemID, STR_Item.ItemName, STR_Item_Specifications.SpecificationName, 
          STR_Item.D1, STR_Item.D2, STR_Item.D3, MAS_SizeType.D1_Code, MAS_SizeType.D2_Code, 
                    MAS_SizeType.D3_Code
      FROM         STR_Item INNER JOIN
                    MAS_SizeType ON STR_Item.SizeTypeID = MAS_SizeType.SizeTypeID INNER JOIN
                    STR_Item_Specifications ON STR_Item.SpecificationID = STR_Item_Specifications.SpecificationID
  
  -------------------- Inserting Data into #TempItems Table ----------------------------
  INSERT INTO #TempItems
  SELECT 
        *
  FROM #MasterTable
  
  ------------------Cursor for All dimensions details into single row for each items ----------------------
  
     DECLARE @DCode VARCHAR(MAX), @Cnt INT
     SET @Cnt = 0
  
     DECLARE ColAdd CURSOR FOR
     SELECT DISTINCT DCodes FROM @Dim
     OPEN ColAdd
     FETCH NEXT FROM ColAdd INTO @DCode
     WHILE (@@FETCH_STATUS = 0)
     BEGIN
    SET @Cnt = 1
    EXECUTE ('ALTER TABLE #TempItems ADD ' + @DCode + ' VARCHAR(50)')
    EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D1
                  FROM #MasterTable M 
              INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D1_Code = ''' + @DCode + '''')
    EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D2
                  FROM #MasterTable M 
              INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D2_Code = ''' + @DCode + '''')
    EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D3
                  FROM #MasterTable M 
              INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D3_Code = ''' + @DCode + '''')
  
  
      FETCH NEXT FROM ColAdd INTO @DCode
      END
      CLOSE ColAdd
      DEALLOCATE ColAdd
      IF (@Cnt = 1)
      BEGIN
  
      SELECT  @DCode = STUFF(( SELECT  DISTINCT '],[' + ltrim(DCodes) 
                          FROM  @Dim 
                          ORDER BY '],[' + ltrim(DCodes)
                          FOR XML PATH('')
  
                                      ), 1, 2, '') + ']'
  
     EXECUTE ('SELECT ItemID, ItemName, Specifications, ' + @DCode + ' FROM #TempItems ORDER BY ItemName') 
     END
  
      DROP TABLE #TempItems
      DROP TABLE #MasterTable
  
  END
  </code></pre>