July 11, 2011 at 10:59 am
Hi
First of all, what I'm trying may be far from the best approach, so I'm open to suggestions:
I have a fairly complex table function that will return a StatusCode and (if appropriate) a description of blocking factors for a scheduled task item, using the TaskItem's ID as an input parameter. What I'd like to be able to do is use the function to update the taskItem's status code, if possible.
Something like:
UPDATE ScheduledTask
SET StatusCode = [Output 1 from FUNCTION],
BlockingFactors = [Output 2 from FUNCTION]
WHERE
SCHEDULEDTASKID = ....
Is this something that can be done? I suspect it can be done with cursors, though the textbooks seem to frown on these.
Thanks!
July 11, 2011 at 11:07 am
You can't use the UPDATE statement inside a function unless the UPDATE statement is directed to a table variable local to the function.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 11, 2011 at 11:12 am
What might be an option for you though, is to wrap your function call into a stored procedure. Have the stored procedure run the function, and have the stored proc store the returned value then use that to run the UPDATE statement.
If the functionality calls for updating several fields, you can consider instead of a scalar-valued function, using a table-valued function, as this would allow you to return a recordset through which you can run the desired update in your stored procedure.
July 11, 2011 at 11:30 am
kramaswamy (7/11/2011)
What might be an option for you though, is to wrap your function call into a stored procedure. Have the stored procedure run the function, and have the stored proc store the returned value then use that to run the UPDATE statement.If the functionality calls for updating several fields, you can consider instead of a scalar-valued function, using a table-valued function, as this would allow you to return a recordset through which you can run the desired update in your stored procedure.
Good info!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply