Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to call the Stored Procedure in the Table Valued Function in SQL Server 2005?

I want to call the Stored Procedure which returns a table in the Table Valued Function. How to create the Table Valued Function?

For Example: "sp_GetTable" is the Stored procedure. It returns a table. Here, I want to write one function called "fn_GetTable"

I want to get the same result which has given by Stored Procedure.

Here is my Stored Procedure:

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 & Inserting TempTable(@Dim) ----------------------

    DECLARE @Dim TABLE (DCodes VARCHAR(100))
    INSERT INTO @Dim SELECT  D1_Code FROM MAS_SizeType WHERE  D1_Code <> ''
    INSERT INTO @Dim SELECT  D2_Code FROM MAS_SizeType WHERE  D2_Code <> ''
    INSERT INTO @Dim SELECT  D3_Code FROM MAS_SizeType WHERE  D3_Code <> ''

------------ 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
like image 804
thevan Avatar asked Jun 14 '11 08:06

thevan


People also ask

Can you call a stored procedure from a table valued function?

Solution 2. No, its not possible. You have to rewrite your procedure to user defined function.

How do you execute a stored procedure with table valued parameters in SQL?

First a Table Variable of User Defined Table Type has to be created of the same schema as that of the Table Valued parameter. Then it is passed as Parameter to the Stored Procedure and the Stored Procedure is executed using the EXEC command in SQL Server.

Can we call stored procedure in function SQL Server?

According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks.

How do you call a stored procedure in SQL Server?

In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.


2 Answers

It's possible to do this from within a Scalar Function, not possible to do from a Table Valued Function. Scalar-valued Functions return a single data-type value and cannot return tables either. Since you cannot select from a stored procedure (you need to EXEC) there is no place for this in a table-valued function.

In general, if the stored procedure returns a, single, result set, define a table-valued function. If the stored procedure computes a scalar value, define a scalar function.

From: Rewriting Stored Procedures as Functions

Re-writing the Stored Procedure as a Table-Valued Function will yield you the most performance.

like image 167
Codesleuth Avatar answered Oct 21 '22 10:10

Codesleuth


Cant be done. You need to rewrite your SP to be a UDF.

Edit

Apparently there are some really ugly workarounds using openquery. But I would recommend rewriting your SP to a function, then you can call the new function from the old SP (if you need to)

like image 20
Magnus Avatar answered Oct 21 '22 08:10

Magnus