CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresuser-defined-functions
SSISPissesMeOff
SSISPissesMeOff

Reputation: 450

Using Table returned from function in SQL Server

I have a SQL Server functions which returns a table. The structure of the table is ID INTEGER, SortOrder INTEGER. 

ALTER FUNCTION dbo.ParseStringByPipe(@InputList VARCHAR(8000) 
RETURNS @SortedList TABLE (ID INTEGER, SortOrder INTEGER))

I have a Stored Procedure dbo.AdjustSortOrder the stored procedure takes in a parameter @InputList VARCHAR(8000) the value of that input parameter will be a list of comma separated numbers in pairs of two which represent the ID and new sort order of that id. ex. 1,2,3,4 (id 1 sort order 2, id 3 sort order 4)

inside the stored procedure, I am making a call to the function. I want to then take the return values from that function and update a table based on the values returned by the function. The date returned will look like this: 

ID                    SortOrder
-------------------------------
1                     5
2                     2
3                     7

I am updating a table called dbo.Orders setting the sortorder field = sort order where the id = the id in the return table. Please correct me if i am wrong but I believe the best way to do this would be an update with an inner join i.e.

UPDATE 
    dbo.Orders 
SET 
    Orders.SortOrder = rt.SortOrder 
FROM 
    dbo.Orders ot 
INNER JOIN 
    SortedList sl
ON
    ot.ID = sl.ID

The first question i have is how do i use the return values from the function inside of a stored procedure, the second question is would an inner join be the most efficient way to accomplish the task at hand? Thank you in advance for all your help.

Upvotes: 0

Views: 141

Answers (1)

ChrisV
ChrisV

Reputation: 1309

You literally just sub in the function and treat it like a table. You need to prefix it with "dbo" generally.

INNER JOIN dbo.SortedList(@InputList) sl ON ot.ID = sl.ID

Upvotes: 1

Related Questions