SQL Server table-valued function? PRAGMA AUTONOMOUS_TRANSACTION

Question

We have some third party software that selects data from an Oracle database using ODBC.

In Oracle, we expose the data as a pipelined function (rather than a table or view) because we want to audit the SELECT. The Oracle pipelined function uses the PRAGMA AUTONOMOUS_TRANSACTION switch in order that we can write a row to an audit table for every row we pipe back to the ODBC Select.

This works brilliantly! But we now have a need to do the same thing from SQL Server rather than Oracle.

I had thought we could do this using a SQL Server table-valued functions in place of Oracle's pipelined functions but we've hit the 'thou shalt not execute DML in a function' law.

We've tried it with a procedure, but the third party software won't have it - it falls over when I tell it to 'EXEC'

Is there a PRAGMA AUTONOMOUS_TRANSACTION equivalent in SQL Server? Or are there any other options for what I want to do?

Answer 1

there's a solution via calling master..xp_cmdshell inside your function the question on dba.stackexchange.com

CREATE FUNCTION loophole(@i int) RETURNS varchar(20) AS
  BEGIN
     DECLARE @sql varchar(MAX),
             @cmd varchar(4000)
     SELECT @sql = ' UPDATE rsci ' +
                   ' SET b = CASE ' + ltrim(str(@i + 1)) +
                   ' WHEN 1 THEN ''Ett'' WHEN 2 THEN ''Två''' +
                   ' WHEN 3 THEN ''Tre'' WHEN 4 THEN ''Fyra''' +
                   ' WHEN 5 THEN ''Fem'' WHEN 6 THEN ''Sex''' +
                   ' WHEN 7 THEN ''Sju'' WHEN 8 THEN ''Åtta''' +
                   ' WHEN 9 THEN ''Nio'' WHEN 10 THEN ''Tio'' END' +
                   ' WHERE a = ' + ltrim(str(@i + 1))
     SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() +
                   ' -Q "' + @sql + '"'
     EXEC master..xp_cmdshell @cmd, 'no_output'
     RETURN (SELECT b FROM rsci WHERE a = @i)
  END