Eseguire Stored Procedures in una funzione UDF (SQL Server)

Un workaround per eseguire stored procedures in functions UDF con Microsoft SQL SERVER. Analizziamo la problematica e scriviamo una .NET external function che permette l’esecuzione di dynamic SQL.

Introduzione

Ultimamente mi sono scontrato con la problematica di eseguire una stored procedure da una funzione SQL UDF (google: How to execute Stored procedures into a UDF function).
L’esecuzione di codice INSERT/UPDATE/DELETE/STORED PROCEDURE che alterano il contesto esterno alla funzione non è possibile by design; è possibile solo apportare modifiche a variabili locali definite nella funzione. Infatti appena tentiamo di alterare il contesto del database otteniamo uno dei seguenti messaggi di errore, rispettivamente se stiamo utilizzando comandi TSQL INSERT/UPDATE/DELETE, chiamando STORED PROCEDURE o cercando di eseguire SQL dinamico:

Invalid use of side-effecting or time-dependent operator in ‘INSERT’ within a function.

Only functions and extended stored procedures can be executed from within a function.

Invalid use of side-effecting or time-dependent operator in ‘EXECUTE STRING’ within a function.

L’utilizzo delle funzioni SQL è utilizzato per leggere e applicare una certa logica ai dati letti, ma non a trasformarli o modificarli. E’difficile pensare che leggendo dei dati da una tabella con una SELECT questi vengano modificati sotto il naso.
Se ci dovessero essere logiche più complesse, come l’utilizzo di tabelle di appoggio temporanee, certo è preferibile e doveroso racchiudere il codice in una stored procedure.

Questo in linea di principio e come best practice.

Caching con una funzione SQL

Un caso che ho dovuto affrontare era quello dell’ottimizzazione delle performance di una serie di applicazioni. Queste, accedendo al DB, ed utilizzando gli strumenti offerti TSQL, utilizzavano tutte, in più punti e ripetutamente una certa funzione, che, accedendo a dati esterni al DB con protocollo TCP/IP, risultava particolarmente lenta a rispondere.

Da qui l’idea di implementare un meccanismo di caching nella funzione stessa; non era assolutamente pensabile cambiare la logica
applicativa modificando il codice e le modalità di interfacciamento al DB. L’operazione doveva essere completamente trasparente per le applicazioni!

L’algoritmo di caching può essere banalmente il seguente:

IF (prima volta)
 BEGIN
  recupera i dati online
  memorizzali nella cache
 ELSE
  recupera i dati dalla cache
 END

Includere tale logica in una funzione SQL è semplice…
Peccato che il significato di memorizzare i dati è quello di utilizzare un’istruzione del tipo INSERT/UPDATE/ST.PROCEDURE e che quindi modifica il contesto esterno alla funzione.

Nel seguito espongo la soluzione di come eseguire una stored procedure in una funzione, descrivendo i passi utili a creare una logica di caching sopra descritta.

Immaginiamo di avere una funzionalità di basso livello (my_very_slow_function) che, dopo essersi connessa con un tal sistema autorizzativo, restituisca i ruoli associati ad un utente.
Vogliamo utilizzare questa funzionalità in una funzione di più alto livello (my_function) che incapsuli la logica (quindi una funzione wrapper)

CREATE FUNCTION [dbo].my_very_slow_function(@login nvarchar(50))
RETURNS TABLE
AS
RETURN
(  
 SELECT 'Administrator' as Role,0 as Type UNION
 SELECT 'Contributor', 0 UNION   
 SELECT 'Public role', 0 UNION   
 SELECT @login, 1
)
SELECT type,role FROM [dbo].my_very_slow_function('DOMAIN\testuser') ORDER BY type,role
type role
0   Administrator 0   Contributor 0   Public role 1   DOMAIN\testuser
CREATE FUNCTION [dbo].[my_function](@login nvarchar(50))
RETURNS
@roles TABLE (role varchar(400), type int)
AS
BEGIN
 INSERT INTO @roles (role,type)
  SELECT role,type FROM my_very_slow_function(@login)
  RETURN
END
SELECT type,role FROM [dbo].my_function('DOMAIN\testuser') ORDER BY type,role

Vogliamo quindi inserire nella funzione wrapper my_function la logica di caching. Creiamo la tabella per la cache e modifichiamo opportunamente la funzione.

CREATE TABLE dbo.MYCACHE (
login VARCHAR(50) NOT NULL,
role VARCHAR(400) NOT NULL,
type INT NOT NULL,
ts datetime DEFAULT getdate(),
PRIMARY KEY (login,role)
)
GO
ALTER FUNCTION [dbo].[my_function](@login nvarchar(50))
RETURNS
@roles TABLE (role varchar(400), type int)
AS
BEGIN
 INSERT INTO @roles (role,type)
  SELECT role,type FROM dbo.MYCACHE
  WHERE login = @login
 IF @@ROWCOUNT = 0
 BEGIN  
  INSERT INTO [dbo].MYCACHE (login,role,type)
   SELECT @login,role,type FROM my_very_slow_function(@login)
  INSERT INTO @roles (role,type)
   SELECT role,type FROM dbo.MYCACHE
   WHERE login = @login
 END  
  RETURN
END

Se tentiamo di modificare la funzione in tal senso otteniamo il seguente errore:

Msg 443, Level 16, State 15, Procedure my_function, Line 11 Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.

Cerchiamo di seguire un’altra strada: eseguire nella funzione una stored procedure contenente la logica del caching.

CREATE PROCEDURE [dbo].MYCACHEINSERT 
 @login VARCHAR(50) 
AS
BEGIN
 INSERT INTO [dbo].MYCACHE (login,role,type)
  select @login,role,type
  FROM dbo.my_very_slow_function(@login) 
END
GO

ALTER FUNCTION [dbo].[my_function](@login nvarchar(50))
RETURNS
@roles TABLE (role varchar(400), type int)
AS
BEGIN
 DECLARE @sql NVARCHAR(400)

 INSERT INTO @roles (role,type)
  SELECT role,type FROM dbo.MYCACHE
  WHERE login = @login
 IF @@ROWCOUNT = 0
 BEGIN  
  exec dbo.MYCACHEINSERT @login

  INSERT INTO @roles (role,type)
   SELECT role,type FROM dbo.MYCACHE
   WHERE login = @login
 END  
  RETURN
END
GO

Questa volta la creazione/aggiornamento non hanno problemi ma si presenta un errore in fase di esecuzione

SELECT type,role FROM [dbo].my_function('DOMAIN\testuser') ORDER BY type,role
Msg 557, Level 16, State 2, Line 1 Only functions and extended stored procedures can be executed from within a function.

Possiamo anche provare a modificare la funzione per farle eseguire codice SQL dinamico, e questa volta otteniamo un errore in fase di modifica.

ALTER FUNCTION [dbo].[my_function](@login nvarchar(50))
RETURNS
@roles TABLE (role varchar(400), type int)
AS
BEGIN
 DECLARE @sql NVARCHAR(400)

 INSERT INTO @roles (role,type)
  SELECT role,type FROM dbo.MYCACHE
  WHERE login = @login
 IF @@ROWCOUNT = 0
 BEGIN  
  SELECT @sql = 'exec dbo.MYCACHEINSERT '''+@login+''''
  exec(@sql)

  INSERT INTO @roles (role,type)
   SELECT role,type FROM dbo.MYCACHE
   WHERE login = @login
 END  
  RETURN
END
Msg 443, Level 16, State 14, Procedure my_function, Line 14 Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

La spiegazione nella difformità di comportamento dal precedente tentativo sta nel fatto che si può usare EXEC giusto per chiamare Extended Stored Procedures, ovvero le stored procedures scritte in C e compilate. Per definizione non si può cambiare lo stato del Database da dentro la funzione UDF, e così non si può costruire SQL dinamico ed eseguirlo con la stored procedure estesa “sp_executesql” (EXEC).

Documentandomi in Internet ho trovato un modo per eseguire uno statement SQL di aggiornamento/inserimento usando la funzione OPENQUERY e un LINKED SERVER che si autoreferenzia:

SELECT count(*) FROM OPENQUERY(LINKED SERVER,'INSERT INTO ....')

Peccato che la stringa di testo deve essere una costante, e non è possibile sostituirla con variabili e/o espressioni.

La via programmatica

L’unico modo by design per risolvere il problema è quello di scrivere un’extended stored procedure in C, correndo il rischio che questa non funzioni più nelle prossime versioni di SQL Server, visto che la scrittura di questo tipo di procedures è deprecata. Scrivendo una external stored procedure in C# (.NET) non si risolve il problema, visto che non viene marcata come “extended” da SQL Server e che quindi non è possibile usarla dentro una funzione UDF.

Anche scrivendo una funzione external in C# ed eseguendo un’operazione INSERT, ottenendo la connessione dal contesto con

SqlConnection conn = new SqlConnection("context connection= true");

otterremmo gli errori iniziali per i quali non è possibile modificare il contesto del DB. L’unico modo alla fine è quello di scrivere una funzione esterna C# ed istanziare una nuova connessione, ma sempre allo stesso database. In tal modo si inganna SQL Server, che non può più riconoscere l’eventuale modifica al contesto esterno.

Ora apriamo Visual Studio e creiamo un nuovo progetto SQL Server “MYUDF” in C#. Nelle proprietà del progetto ricodiamoci di impostare l’opzione “Sign the assembly”.

Poi aggiungiamo un nuovo file al progetto dti tipo Funzione UDF:

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions {     [Microsoft.SqlServer.Server.SqlFunction(Name = "ExecuteSQL", 
 DataAccess = DataAccessKind.Read, IsDeterministic = false)]
    public static int ExecuteSQL(SqlString sql, SqlString connstr)     {         using (SqlConnection conn = new SqlConnection(connstr.Value))         {             int res=0;             try             {                                             conn.Open();                                SqlCommand cmd = conn.CreateCommand();                 cmd.CommandText = sql.Value;                                res = (int)cmd.ExecuteNonQuery();                            }             catch (Exception ex)             {                                throw;             }             finally {                                                conn.Close();                            }             return res;         }     } }

La funzione riceve dall’input due parametri: la stringa contenente il codice SQL e quella per la connessione ad un DB. I controlli per la sicurezza e la gestione dell’errore è stata volutamente trascurata per semplificare la lettura. Al build del progetto verrà prodotto l’assembly MYUDF.dll, che dovrà essere copiato sul database server in una certa cartella, diciamo C:\TEMP. Creiamo e carichiamo l’assembly nel DB con il comando TSQL “CREATE ASSEMBLY”:

L’istruzione CREATE ASSEMBLY MYUDF può fallire e produrre i seguenti messaggi d’errore:

A) Msg 10327, Level 14, State 1, Line 1 

CREATE ASSEMBLY for assembly ‘MYUDF’ failed because assembly ‘MYUDF’ is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

ALTER DATABASE MYDB set trustworthy on;

B) Msg 0, Level 20, State 0, Line 0 

Errore grave durante l’esecuzione del comando corrente. Annullare i risultati eventuali.

Questo è un BUG noto (almeno per SQL2005), basta eseguire il seguente comando: exec sp_changedbowner ‘<username of the db attacher, ‘sa’, or any windows authenticated login>’

 

EXEC sp_changedbowner 'sa'

CREATE ASSEMBLY MYUDF
AUTHORIZATION [dbo] FROM 'C:\TEMP\MYUDF.dll' WITH PERMISSION_SET = UNSAFE;

CREATE FUNCTION [dbo].[ExecuteSQL](@sql [nvarchar](4000),@conn [nvarchar](200))
RETURNS int
AS EXTERNAL NAME MYUDF.[UserDefinedFunctions].ExecuteSQL
GRANT EXECUTE ON [dbo].[ExecuteSQL] TO [public]

Abbiamo appena creato la funzione UDF .NET, ed ora ci prepariamo a testarla:

select [dbo].[ExecuteSQL]('exec dbo.MYCACHEINSERT ''WORKGROUP\user1''','Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=False;user id=dbu;password=dbu;')

Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'ExecuteSQL': System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'MYCACHEINSERT', database 'MYDB', schema 'dbo'.
GRANT EXECUTE ON [dbo].MYCACHEINSERT TO [dbu]

Ora che abbiamo dato i permessi “rieseguiamo” la funzione, che “magicamente” ritorna i risultati attesi. Infatti nella tabella ci sono 4 righe. Bisogna far notare che è stato creata una login specifica “dbu” per inserire i dati nella tabella di cache e solo per quello scopo. Poi integriamo la chiamata ad ExecuteSQL in my_function.

SELECT * FROM dbo.MYCACHE
login  role  type ts WORKGROUP\user1 Administrator  0  2010-04-25 12:08:13.513 WORKGROUP\user1 Contributor  0  2010-04-25 12:08:13.513 WORKGROUP\user1 Public role  0  2010-04-25 12:08:13.513 WORKGROUP\user1 WORKGROUP\user1 1  2010-04-25 12:08:13.513
SELECT count(*) FROM dbo.MYCACHE
4

ALTER FUNCTION [dbo].[my_function](@login nvarchar(50))
RETURNS
@roles TABLE (role varchar(400), type int)
AS
BEGIN
 DECLARE @sql NVARCHAR(400)
 DECLARE @conn NVARCHAR(200)
 DECLARE @count INT

 INSERT INTO @roles (role,type)
  SELECT role,type FROM dbo.MYCACHE
  WHERE login = @login
 IF @@ROWCOUNT = 0
 BEGIN  
  SELECT @sql = 'exec dbo.MYCACHEINSERT '''+@login+'''',
   @conn = 'Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=False;user id=dbu;password=dbu;'
  SELECT @count = [dbo].[ExecuteSQL] (@sql,@conn)

  INSERT INTO @roles (role,type)
   SELECT role,type FROM dbo.MYCACHE
   WHERE login = @login
 END  
  RETURN
END

SELECT type,role FROM [dbo].my_function('DOMAIN\testuser') ORDER BY type,role
type role
0  Administrator 0  Contributor 0  Public role 1  DOMAIN\testuser 
SELECT count(*) FROM dbo.MYCACHE
8
Verifica effettuata e possiamo affermare che… il gioco è fatto! A questo punto abbiamo terminato di implementare il caching che ci prefiggevamo. Infatti le chiamate successive alla prima sono molto più veloci nell’esecuzione. Ora la funzione wrapper my_function può essere utilizzata in altri contesti, in altre funzioni o stored procedures.

 

 

 

Per esempio:  
 
 
 

 

CREATE FUNCTION [dbo].[my_function2](@login nvarchar(50),@param1 varchar(50)) RETURNS TABLE AS RETURN (   SELECT count(*) as N, @param1 as M FROM [dbo].my_function(@login)   ) SELECT * FROM [dbo].[my_function2]('DOMAIN\test2','param') N M 4 param

 

Continuando a sperimentare

Di seguito espongo una problematica che ho incontrato nell’utilizzare la tecnica sopra esposta in una stored procedure che utilizza un oggetto non locale quale una tabella temporanea (ricordo che è comunque una TABLE nel tempdb). Con simili oggetti si verrebbe a creare una transazione distribuita, ma SQL Server, capendo che la connessione istanziata dal codice .NET, individua un conflitto di sessioni. E da qui il messaggio di errore di cui sotto.

CREATE PROCEDURE [dbo].[my_procedure]    @login varchar(50),  @param1 varchar(50) AS BEGIN   SET NOCOUNT ON;  SELECT count(*) as N, @param1 as M FROM [dbo].my_function(@login)   END exec [dbo].[my_procedure] 'DOMAIN\test2','param' N M 4 param 
ALTER PROCEDURE [dbo].[my_procedure]    @login varchar(50),  @param1 varchar(50) AS BEGIN   SET NOCOUNT ON;  SELECT count(*) as N, @param1 as M INTO #temp FROM [dbo].my_function(@login)    SELECT * FROm #temp END GO exec [dbo].[my_procedure] 'DOMAIN\test5','param'
Msg 6522, Level 16, State 1, Procedure my_procedure, Line 7 A .NET Framework error occurred during execution of user defined routine or aggregate 'ExecuteSQL': System.Data.SqlClient.SqlException: Transaction context in use by another session.

La soluzione è più semplice del previsto: poichè SQL Server tenta implicitamente di costruire una transazione distribuita, il workaround è quello di inibire a priori tale possibilità attraverso il parametro opzionale Enlist nella connectionstring. E infatti, dopo la modifica della stringa di connessione, l’esecuzione funziona come ci si aspetta, ritornando i valori aspettati.

ALTER FUNCTION [dbo].[my_function](@login nvarchar(50)) RETURNS @roles TABLE (role varchar(400), type int) AS BEGIN  DECLARE @sql NVARCHAR(400)  DECLARE @conn NVARCHAR(200)  DECLARE @count INT  INSERT INTO @roles (role,type)   SELECT role,type FROM dbo.MYCACHE   WHERE login = @login  IF @@ROWCOUNT = 0  BEGIN     SELECT @sql = 'exec dbo.MYCACHEINSERT '''+@login+'''',    @conn = 'Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=False;user id=dbu;password=dbu;Enlist=no'   SELECT @count = [dbo].[ExecuteSQL] (@sql,@conn)   INSERT INTO @roles (role,type)    SELECT role,type FROM dbo.MYCACHE    WHERE login = @login  END     RETURN END exec [dbo].[my_procedure] 'DOMAIN\test7','param'
N M 4 param 
 

Conclusioni

L’utilizzo di stored procedures in funzioni è inibito in SQL Server by design, ma creando una external UDF in C#.NET è possibile aggirare l’ostacolo. E’stato presentato il codice e un esempio di come implementare il caching per una funzione “lenta”.  

 

Questa voce è stata pubblicata in Software, SQL Server e contrassegnata con , . Contrassegna il permalink.

2 risposte a Eseguire Stored Procedures in una funzione UDF (SQL Server)

  1. maurizio scrive:

    aiutoooooo

    non riesco ad impostare una stored procedure e richiamarla da asp classic

    • Mauro Dalfreddo scrive:

      Mi sembra una richiesta un po’ troppo generica; devi essere un po’più preciso e dettagliare meglio… grazie

Lascia un Commento

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *


*

È possibile utilizzare questi tag ed attributi XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>