446,275 Members | 1,937 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,275 IT Pros & Developers. It's quick & easy.

# How to update using a function

 P: n/a Take a table, where not all the columns are populated: CREATE TABLE #T (A int, B int, C int, D int) INSERT #T (A,B) VALUES (1,2) INSERT #T (A,B) VALUES (3,4) INSERT #T (A,B) VALUES (5,6) INSERT #T (A,B) VALUES (7,8) INSERT #T (A,B) VALUES (9,10) The values for C and D can be computed as functions of A and B. For this example, let's say they are twice A and three times B, respectively: CREATE FUNCTION dbo.F(@A int,@B int) RETURNS @Tbl TABLE (X int, Y int) AS BEGIN INSERT @Tbl (X,Y) VALUES (@A*2, @B*3) RETURN END Now we use the function to compute the other columns: UPDATE #T SET C=X, D=Y FROM dbo.F(A,B) Right? Well, no. Instead, I get this message: Server: Msg 155, Level 15, State 1, Line 2 'A' is not a recognized OPTIMIZER LOCK HINTS option. Any suggestions? I would like to use this structure, if possible. Jim Geissman Countrywide Home Loans Jul 20 '05 #1
7 Replies

 P: n/a "Jim Geissman" wrote in message news:b8**************************@posting.google.c om... Take a table, where not all the columns are populated: CREATE TABLE #T (A int, B int, C int, D int) INSERT #T (A,B) VALUES (1,2) INSERT #T (A,B) VALUES (3,4) INSERT #T (A,B) VALUES (5,6) INSERT #T (A,B) VALUES (7,8) INSERT #T (A,B) VALUES (9,10) The values for C and D can be computed as functions of A and B. For this example, let's say they are twice A and three times B, respectively: CREATE FUNCTION dbo.F(@A int,@B int) RETURNS @Tbl TABLE (X int, Y int) AS BEGIN INSERT @Tbl (X,Y) VALUES (@A*2, @B*3) RETURN END Now we use the function to compute the other columns: UPDATE #T SET C=X, D=Y FROM dbo.F(A,B) Right? Well, no. Instead, I get this message: Server: Msg 155, Level 15, State 1, Line 2 'A' is not a recognized OPTIMIZER LOCK HINTS option. Any suggestions? I would like to use this structure, if possible. Jim Geissman Countrywide Home Loans Unfortunately, this doesn't work since you can't use column names as parameters in this situation. In this specific case, you could simply create C and D as computed columns: CREATE TABLE #T (A int, B int, C as A*2, D as B*3) Alternatively, you could use scalar UDFs: create function dbo.F2(@i int) returns int as begin return @i*2 end create function dbo.F3(@i int) returns int as begin return @i*3 end update #T set C=dbo.F2(A), D=dbo.F3(B) But scalar functions perform badly on large data sets, because they are invoked per-row, so this might not be appropriate. If neither of these options are useful, then the simplest solution may just be a stored procedure which does the UPDATE. Simon Jul 20 '05 #2

 P: n/a You need a scalar UDF instead of a table-valued UDF: CREATE FUNCTION dbo.FA(@A INTEGER) RETURNS INTEGER AS BEGIN RETURN @A*2 END GO CREATE FUNCTION dbo.FB(@B INTEGER) RETURNS INTEGER AS BEGIN RETURN @B*3 END GO UPDATE #T SET c=dbo.FA(a), d=dbo.FB(b) Don't store calculated results in the table if you can help it. If C and D are always calculated from A and B then just drop the C and D columns and do the calculation in a view or query instead. -- David Portas SQL Server MVP -- Jul 20 '05 #3

 P: n/a > Thanks for the suggestions. Unfortunately, in the real case, the function values are a property price index and its estimated reliability, which are computed from relatively elaborate lookups and calculations based on zip code, effective date and property type. The key point to me is that variable names can't be used as function parameters in a case like this. Too bad. I'm not sure I understand why that is a problem. UDF parameters are passed by value not by reference and in this respect my function is no different from the one you posted - I just used different names. If you post your function code (also DDL and sample data INSERT statements) maybe we can help you rewrite it exactly as required. Also consider Simon's point about the performance overhead of a UDF. Possibly you can use an UPDATE statement, query or view to achieve the same result without using a function. -- David Portas SQL Server MVP -- Jul 20 '05 #4

 P: n/a > Don't store calculated results in the table if you can help it. If C and D are always calculated from A and B then just drop the C and D columns and do the calculation in a view or query instead. Thanks for the suggestions. Unfortunately, in the real case, the function values are a property price index and its estimated reliability, which are computed from relatively elaborate lookups and calculations based on zip code, effective date and property type. The key point to me is that variable names can't be used as function parameters in a case like this. Too bad. Jim Jul 20 '05 #5

 P: n/a "David Portas" wrote in message news:... Thanks for the suggestions. Unfortunately, in the real case, the function values are a property price index and its estimated reliability, which are computed from relatively elaborate lookups and calculations based on zip code, effective date and property type. The key point to me is that variable names can't be used as function parameters in a case like this. Too bad. I'm not sure I understand why that is a problem. UDF parameters are passed by value not by reference and in this respect my function is no different from the one you posted - I just used different names. If you post your function code (also DDL and sample data INSERT statements) maybe we can help you rewrite it exactly as required. Also consider Simon's point about the performance overhead of a UDF. Possibly you can use an UPDATE statement, query or view to achieve the same result without using a function. Sure, I can do this with two separate function calls, each of which returns a single scalar value. However, computing value A and value B both visit a lot of the same lookups and computations, so it seems to me there is duplication of effort, hence lower than optimum efficiency. Because I'm looking at doing this approx 60 million times, I would like to use that work to compute both values at once. I would also like to use the elegance of a function, which makes the code easier to read. Two function calls preserve the elegance by hiding the details, and that's what I did last time, and will do this time. I was simply hoping to combine all that work and retrieve both results from one call. I don't care about the extra time required to come up with the values. I don't do the work -- a computer does it, and computers don't complain about unnecessary work. I will simply adjust the schedule. But it still seems odd that I can use the function approach to get values one at a time, but not two at a time. Jim Jul 20 '05 #6

 P: n/a Jim Geissman (ji**********@countrywide.com) writes: Sure, I can do this with two separate function calls, each of which returns a single scalar value. However, computing value A and value B both visit a lot of the same lookups and computations, so it seems to me there is duplication of effort, hence lower than optimum efficiency. Because I'm looking at doing this approx 60 million times, I would like to use that work to compute both values at once. I would also like to use the elegance of a function, which makes the code easier to read. Two function calls preserve the elegance by hiding the details, and that's what I did last time, and will do this time. I was simply hoping to combine all that work and retrieve both results from one call. I don't care about the extra time required to come up with the values. I don't do the work -- a computer does it, and computers don't complain about unnecessary work. I will simply adjust the schedule. But it still seems odd that I can use the function approach to get values one at a time, but not two at a time. There is some new syntax in the upcoming version of SQL Server that permits you to call a table-valued function and passing column values to it. I have not studied it in detail, though. Anyway, this is SQL Server, and you could do one of two things that is better than having two UDFs: 1) Return the two values in one, as a string, and then split that string in the UPDATE statement. Sure is not going to give you nice code, but at least you are not doing the compuation twice. 2) Keep your table-valued function, and set up a cursor on the table to update, and update one row at a time: UPDATE tbl SET col3 = f.x, col4, f.y FROM tbl t CROSS JOIN f(@col1, @col2) f WHERE t.keycol = @keyval This may sound horrendeously inneffecient, but if you call an scalar UDF in a set-based statement, you get something cursor-like behind the scenes anyway. Then, of course, you could devise the computation in your function so that it works on all values in the table at once, but that may prove to be a real challenge. -- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp Jul 20 '05 #7

 P: n/a Thanks a lot, Erland! Jim Jul 20 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.