Niedawno opublikowałem zestaw gotowych skryptów SQL, które mogą się przydać w konkretnych sytuacjach lub posłużyć jako wzorzec w innych przypadkach. Ponieważ – w ramach porządków – znowu uzbierało mi się kilka takich skryptów – i je postanowiłem zaprezentować. Tym sposobem samoistnie powstał mi cykl ;).

Na początek przykład sklejenia tekstowej zawartości kolumny w inny sposób niż podany poprzednio (bez użycia XML).

declare @aggregate varchar(max) = '';

if object_id('tempdb..#content') is not null
	drop table #content;
create table #content (content varchar(50));
insert into #content
values
	('zero'), ('jeden'), ('dwa'), ('trzy'), ('cztery'),
	('pięć'), ('sześć'), ('siedem'), ('osiem'), ('dziewięć');
 
select
  @aggregate = @aggregate + content + ' '
from #content;
 
select RTrim(@aggregate) as result;

Zdarza się, że dobrze byłoby przechować w tabeli zestaw danych zwracany przez procedurę składowaną. Załóżmy, że jako przykład posłuży trywialna procedura zwracająca listę obiektów bazy danych, czyli:

create procedure [dbo].[ResultSet]
as
begin
	select * from sys.objects;
	return;
end;

Aby można było przekazać zwracany przez procedurę zestaw danych do tabeli, taka tabela musi już istnieć i mieć strukturę zgodną ze zwracanym zestawem danych. Oto stosowny skrypt:

if object_id('tempdb..#Tymczasowa') is not null
	drop table #Tymczasowa;
select top 0 * into #Tymczasowa from sys.objects; -- tabela na wynik procedury
insert into #Tymczasowa
	exec dbo.ResultSet; -- przekazanie wyniku do tabeli

Oczywiście w tym przypadku jest dość łatwo przygotować tabelę, bo wystarczy skorzystać z już istniejącej. W innych przypadkach trzeba będzie taką tabelę przygotować własnoręcznie. Tak np. może być, jeśli zechcemy przechować wyniki procedury systemowej sp_who2.

    create table #Sessions (
      SPID int, [Status] varchar(20), [Login] varchar(50), HostName varchar(100), BlkBy varchar(100),
      DBName varchar(100), Command varchar(100), CPUTime int, DiskIO int, LastBatch varchar(20),
      ProgramName varchar(100), SPID2 int, REQUESTID int
    );
    insert into #Sessions
        exec sp_who2;

Trochę to uciążliwe, nieprawdaż? Dodatkowo, jeśli procedura będzie zwracała dwa zestawy wyników:

create procedure [dbo].[ResultSet]
as
begin
	select * from sys.objects;
	select * from sys.databases;
	return;
end;

wówczas zaprezentowane skrypty przestaną działać.

Na szczęście zarówno wspomnianą uciążliwość, jak i problem wielu wyników, można zniwelować – niemniej niesie to pewne koszty w innych obszarach.

Co zatem uczynić, aby nie tworzyć tabeli przed wykonaniem procedury i uzyskać ją automatycznie, a jednocześnie nie obawiać się wielu zestawów wyników i po prostu pobierać pierwszy z nich? Należy użyć funkcji OpenDataSource:

select *
into #Result
from OpenRowset('SQLNCLI', 'Server=.\SQLEXPRESS2K8R2;Trusted_Connection=yes;', 'exec Test.dbo.ResultSet;');

Dodatkowe koszty o których była mowa, to:

  • konieczność określenia nazwy serwera (chyba, że jest to instancja domyślna, wówczas nazwa to (local))
  • konieczność określenia bazy danych (w ostatnim literale widać poprzedzenie wywołania procedury, oprócz nazwy schematu, także nazwą bazy) – niestety nie można tego „skleić” w żaden automatyczny sposób, ani użyć zmiennej, bo OpenDataSource nie dopuszcza zmiennych w swoich parametrach
  • serwer SQL musi mieć włączone wykonywanie doraźnie rozprowadzanych zapytań (Ad Hoc Distributed Queries)

Skoro już mowa o OpenDataSource, to warto wspomnieć o funkcji OpenRowSet. Ma ona wiele zastosowań, ale jedno jest szczególnie interesujące. Otóż za jej pomocą można dokonać eksportu danych z poziomu SQL Serwera. Najłatwiej zrobić to do pliku tekstowego rozdzielanego przecinkami, inne formaty wymagają trochę więcej zabiegów. Oto jak wygląda taki eksport:

if object_id('tempdb..#content') is not null
	drop table #content;
create table #content (content varchar(50));
insert into #content
values
	('zero'), ('jeden'), ('dwa'), ('trzy'), ('cztery'),
	('pięć'), ('sześć'), ('siedem'), ('osiem'), ('dziewięć');
insert into OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\FolderEksportu\', 'select * from [eksport#csv]')
select *
from #content;

Niestety nie jest tu aż tak różowo, albowiem plik eksport.csv musi istnieć na dysku. Dodatkowo i w tym przypadku serwer SQL musi mieć włączone wykonywanie wykonywanie doraźnie rozprowadzanych zapytań (Ad Hoc Distributed Queries). Ale, można zrobić rzecz następującą:

EXEC master..xp_cmdshell 'copy /Y nul C:\FolderEksportu\eksport.csv';

i od tego momentu plik będzie istniał. Niemniej nadal trzeba ponieść dodatkowe koszty, w tym wypadku konieczne jest włączenie opcji xp_cmdshell, co obniża bezpieczeństwo serwera SQL.

Na dzisiaj to wszystkie „gotowce”, jakie chciałem zaprezentować, ale z pewnością nie wszystkie, które w ogóle zaprezentować zamierzam. Należy zatem oczekiwać kolejnych części tego nieoczekiwanego cyklu :).