Home > About WITH RESULT SETS clause (T-SQL) in SQL Server Code-Name “Denali”

About WITH RESULT SETS clause (T-SQL) in SQL Server Code-Name “Denali”

Sergio Govoni 9 Dicembre 2010 Senza categoria 2,258 Views

In questo articolo parleremo della nuova clausola WITH RESULT SETS che sarà possibile utilizzate con il comando EXECUTE (T-SQL) a partire dalla prossima versione di SQL Server, oggi in CTP1 con il Code-Name "Denali".

La nuova clausola WITH RESULT SETS, applicabile al comando EXECUTE, permette di ri-definire (cambiare) il nome e il tipo di dato per ogni colonna presente nel result set del comando EXECUTE (che ha invocato ad esempio una stored procedure).

Vediamo la nuova clausola WITH RESULT SETS in azione, negli esempi che abbiamo preparato, utilizzeremo la tabella dbo.Product creata nel database di sistema [tempdb]. La tabella dbo.Product è acceduta dalla stored procedure dbo.usp_get_product_for_sell() che restituisce in output la lista dei prodotti vendibili in determinato momento.

Il seguente frammento di codice T-SQL esegue la creazione della tabella dbo.Product (sul [tempdb]) e l’inserimento di alcuni prodotti di test commercializzati ad un’ipotetica azienda alimentare.

use [tempdb];
go

————————————————————————
— Setup table
————————————————————————

if OBJECT_ID(‘dbo.Product’, ‘U’) is not null
  drop table dbo.Product;
go

create table dbo.Product
(
  ProductID sysname not null primary key
  ,SellStartDate date not null
  ,SellEndDate date not null
  ,ProductNote varchar(40) null
  ,ListPrice decimal(8, 2) not null
  ,SafetyStockLevel integer not null
);
go

— Insert some test data
insert into dbo.Product
(ProductID, SellStartDate, SellEndDate, ProductNote, ListPrice, SafetyStockLevel)
values
(‘seattle crab’, ‘20101206’, ‘99991231’, ‘1 kg’, 16.2 , 4),
(‘king salmon’, ‘20101206’, ‘20111231’, ‘2 kg’, 11.5, 10),
(‘coffee’, ‘20101208’, ‘20110630’, null, 5.0, 80),
(‘blueberry scone’, ‘20101210’, ‘20110630’, ‘0.1 kg’, 1.30, 8),
(‘blueberry muffin’, ‘20101211’, ‘20110630’, ‘0.1 kg’, 1.40, 15);
go

Visualizziamo il contenuto della tabella dbo.Product per familiarizzare con i dati.

select * from dbo.Product;
go

Figura 1 – Dati di test contenuti nella tabella dbo.Product

Definiamo ora la stored procedure dbo.usp_get_product_for_sell() utilizzata dagli operatori commerciali per conoscere i prodotti vendibili (dall’azienda) in un determinato momento. La stored procedure accetta in input due parametri: @vDate che rappresenta il giorno per il quale si desidera conoscere i prodotti vendibili e @vPrice che rappresenta il parametro di filtro sulla colonna ListPrice.

Il seguente frammento di codice T-SQL permette di creare la stored procedure dbo.usp_get_product_for_sell() nel database [tempdb]:

————————————————————————
— Setup stored procedure
————————————————————————

use [tempdb];
go

if OBJECT_ID(‘dbo.usp_get_product_for_sell’, ‘P’) is not null
  drop procedure dbo.usp_get_product_for_sell;
go

create procedure dbo.usp_get_product_for_sell
(
  @vDate as date
  ,@vPrice as decimal (8, 2) = 0
)
as
begin
  /*
    Name: dbo.usp_get_product_for_sell()
  */

  if (isnull(@vDate, ”) = ”)
  begin
    print (‘Error dbo.usp_get_product_for_sell(): invalid value for parameter @vDate’);
 return;
  end;

  — …
  — …

  select
    ProductID
 ,ProductNote
 ,SellStartDate
 ,SellEndDate
  from
    dbo.Product
  where
    (@vDate between SellStartDate and SellEndDate);

  — …
  — …

  if (ISNULL(@vPrice, 0) > 0)
  begin
    select
      ProductID
      ,ListPrice
    from
      dbo.Product
    where
      (ListPrice <= @vPrice)
   and (@vDate between SellStartDate and SellEndDate);
  end;
end;
go

Eseguiamo ora la stored procedure dbo.usp_get_product_for_sell() per ottenere in output gli articoli vendibili in data 06/12/2010, utilizzeremo quindi il comando EXECUTE specificando la clausola WITH RESULT SETS per assegnare un nome ed un tipo (di dato) specifico ad ogni colonna presente nel result set.

— Single WITH RESULT SETS
exec dbo.usp_get_product_for_sell @vDate = ‘20101206’
with result sets
(
  — First result sets
  — Column name and data type
  (
    ProductName varchar(20)
 ,Note varchar(40)
 ,SellBy date
 ,SellEnd date
  )
);
go

L’output ottenuto è illustrato in figura 2, osserviamo come le colonne del result set estratto all’interno della stored procedure siano state rinominate, come specificato nella clausola WITH RESULT SETS.

Figura 2 – EXECUTE statement con clausola WITH RESULT SETS

Oltre agli articoli vendibili a una certa data, nel prossimo esempio desideriamo ricevere in output un result set aggiuntivo che deve contenere, come nel caso del primo result set, gli articoli vendibili alla data passata (nel parametro @vDate), ma che hanno anche prezzo di listino minore o uguale a un determinato valore, passato alla stored procedure nel parametro @vPrice.

Nel seguente frammento di codice T-SQL illustriamo come sia possibile definire il nome delle colonne e il tipo, anche per il secondo result set (con la stessa logica mutuata per il primo).

— Multiple WITH RESULT SETS
exec dbo.usp_get_product_for_sell
  @vDate = ‘20101208’
  ,@vPrice = 5
with result sets
(
  — First result sets
  — Column name and data type
  (
    ProductName varchar(20)
 ,Note varchar(40)
 ,SellBy date
 ,SellEnd date
  ),

  — Second result sets
  — Column name and data type, again
  (
    ProductName varchar(20)
 ,ListPrice decimal(8, 2)
  )
);
go

L’output ottenuto è illustrato in figura 3.

Figura 3 – EXECUTE statement con clausola WITH RESULT SETS (multiplo)

Nel prossimo esempio vedremo come sia possibile definire, per un comando EXECUTE, un result set di tipo XML, da utilizzare in presenza di comandi SELECT che includono la clausola FOR XML. Per proseguire con l’esempio, sarà quindi necessario modificare la stored procedure dbo.usp_get_product_for_sell() per aggiungere la clausola FOR XML nel comando SELECT, di estrazione degli articoli vendibili ad una certa data, con prezzo di listino minore o uguale al valore specificato nel parametro @vPrice. Tale selezione è ora restituita nel secondo result set della stored procedure.

Il seguente frammento di codice T-SQL contiene il comando ALTER PROCEDURE con cui modifichiamo l’oggetto dbo.usp_get_product_for_sell() aggiungendo la clausola FOR XML come in precedenza descritto:

— WITH RESULT SETS as FOR XML
use [tempdb];
go

alter procedure dbo.usp_get_product_for_sell
(
  @vDate as date
  ,@vPrice as decimal (8, 2) = 0
)
as
begin
  /*
    Name: dbo.usp_get_product_for_sell()
  */

  if (isnull(@vDate, ”) = ”)
  begin
    print (‘Error dbo.usp_get_product_for_sell(): invalid value for parameter @vDate’);
 return;
  end;

  — …
  — …

  select
    ProductID
 ,ProductNote
 ,SellStartDate
 ,SellEndDate
  from
    dbo.Product
  where
    (@vDate between SellStartDate and SellEndDate);

  — …
  — …

  if (ISNULL(@vPrice, 0) > 0)
  begin
    select
      ProductID
      ,ListPrice
    from
      dbo.Product
    where
      (ListPrice <= @vPrice)
   and (@vDate between SellStartDate and SellEndDate)
 for xml path(‘root’);
  end;
end;
go

Eseguiamo ora la nuova versione della stored procedure, prestando particolare attenzione alla definizione del secondo result set; nella clausola WITH RESULT SETS compare, infatti, l’opzione AS FOR XML:

exec dbo.usp_get_product_for_sell
  @vDate = ‘20101208’
  ,@vPrice = 5
with result sets
(
  — First result sets
  — Column name and data type
  (
    ProductName varchar(20)
 ,Note varchar(40)
 ,SellBy date
 ,SellEnd date
  ),

  — Second result sets
  — as XML
  as for xml
);
go

L’output è illustrato in figura 4:

Figura 4 – EXECUTE statement con clausola WITH RESULT SETS (multiplo) e opzione AS FOR XML

 

Conclusioni

In SQL Server “Denali”, la clausola WITH RESULT SETS permette di definire i metadati restituiti da uno statement EXECUTE.

 

Pulizia DB

————————————————————————
— Pulizia DB
————————————————————————

use [tempdb];
go

if OBJECT_ID(‘dbo.usp_get_product_for_sell’, ‘P’) is not null
  drop procedure dbo.usp_get_product_for_sell;
go

if OBJECT_ID(‘dbo.Product’, ‘U’) is not null
  drop table dbo.Product;
go

 

Tags

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Azure Key Vault e certificati code-signing: Strategie per la conformità post 1° Giugno 2023!

In questi giorni, ho avuto l’opportunità di esplorare il rinnovo di un certificato di code-signing …

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2024