UDFs That Modify Data
One main difference between a stored procedure and a function is that, by definition, stored procedures can change the state of the database (modify permanent data; create, alter, or drop permanent objects; and so on) and functions can't. Functions can only perform calculations and return the results. But some programmers have found a back door that lets them modify permanent data from within a UDF by using the OPENQUERY() rowset function. The OPENQUERY() function accepts a linked-server name and a character string as input arguments, submits the code to the linked server as pass-through code without parsing or examining it, and returns the results of running the code against the linked server to the calling program. OPENQUERY() is allowed within a UDF, and because SQL Server doesn't examine the character string you provided to the function as an argument, you can use OPENQUERY() to modify data on the linked server. You can even refer to your local server as a linked server by turning on the Data Access server option:
EXEC sp_server
option [server_name], 'Data Access',
true
As an example of a function that modifies data, I'll use a much-requested implementation of a custom sequence function. Suppose you have two invoice tables: Invoices1 holds invoices for domestic customers, and Invoices2 holds invoices for foreign customers. Run the following code to create both tables in the tempdb database:
USE tempdb
CREATE TABLE Invoices1(invoiceid
int, datacol varchar(10) NOT
NULL)
CREATE TABLE Invoices2(invoiceid
int, datacol varchar (10) NOT
NULL)
You want SQL Server to generate the invoiceid values similar to the way it generates IDENTITY values but across two tables instead of one. So, a new invoice inserted into either of the tables should receive the next incremented invoice ID. One technique that programmers use in such a case is to create a table that has just one column, which holds the last sequence value used. Run the following code to create the custom sequence table Seq and populate it with the seed sequence value:
CREATE TABLE Seq(val int NOT NULL
DEFAULT 0)
INSERT INTO Seq DEFAULT VALUES
From your application code (or from a stored procedure or a trigger), you'd use the following code to increment the custom sequence value, then retrieve the new value and use it in an INSERT statement:
DECLARE @invid AS int
UPDATE Seq SET @invid = val =
val + 1
INSERT INTO Invoices1
VALUES(@invid, 'a')
After running the preceding code, query the Seq and Invoices1 tables. Notice that the sequence value in Seq is now 1 and that the new invoice has 1 as its invoiceid value.
However, you can't use this technique in a multirow INSERT or UPDATE query because coming up with a multirow modification that increments a sequence for each row of the modification can be highly complex and inefficient. Suppose you wanted to issue an INSERT statement to assign new invoiceid values automatically. Usually, a programmer's first thought in this situation is to create a function that increments the sequence value and returns it to the invoking code, as Listing 3 shows. However, as I mentioned, you can't modify permanent data in the database from within a function, so Listing 3's code returns the following error:
Server: Msg 443, Level 16, State 2, Procedure
fn_getinvid, Line 6
Invalid use of 'UPDATE' within a function.
But if you use OPENQUERY() to perform the update, as Listing 4 shows, the UDF works. The fn_getinvid1 UDF uses the OPENQUERY() function to issue an UPDATE statement that increments the sequence value. Then, the UDF commits the transaction because, by default, OPENQUERY() opens an implicit transaction behind the scenes when submitting any code to the target server. Finally, the code in the OPENQUERY() function returns the new sequence value as a table containing a single column called newinvid and one row. The fn_getinvid1() UDF returns the new sequence value to the caller of the function.
After creating fn_getinvid1(), test it by running the multirow INSERT queries that Listing 5 shows against Invoices1 and Invoices2. Query both tables, and notice that all rows received different invoiceid values no matter which table they were in. To use the UDF to test a multirow update, run the following code:
UPDATE Invoices1 SET invoiceid =
dbo.fn_getinvid1()
WHERE datacol LIKE
'A%'
Then, select all rows from Invoices1, and notice that all rows containing a string starting with A in the datacol column received new invoice IDs.
The fn_getinvid1() function can run into concurrency problems when multiple processes invoke it at the same time. You can avoid these problems by using the IDENTITY property with the val column in the custom sequence table. Instead of updating the row, with each invocation, the function will insert a row, roll back the transaction, and return the value of SCOPE_IDENTITY()—a function that returns the last IDENTITY value inserted by the session in the current scope. (Routines that the calling code invokes aren't considered part of the code's scope.) The rollback trick spares you from having to keep lots of unused rows in the sequence table or from needing to delete its content periodically. Rollbacks don't affect SCOPE_IDENTITY(), so you effectively get what you need—a new sequence value—while the sequence table remains empty.
Run the code that Listing 6 shows to create a new sequence table that uses the IDENTITY column property for the sequence value and to create a second version of the function, fn_getinvid2(). You use this function the same way you use the fn_getinvid1() function, so you can test it by using similar INSERT and UPDATE queries that you used to test the first version.
Missing Features
This article covers only a couple of UDF-related back doors. The missing abilities to create special UDFs that you can invoke from any database and to modify data through UDFs would be useful as supported features in SQL Server. If you think that SQL Server should support certain features that are currently back doors only, send them to sqlwish@microsoft.com. SQL Server program managers review requests and consider them for future releases of SQL Server.