0

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.

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

1 Comment

thank you, i guess i was way over complicating it in my mind.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.