Zwracanie rezultatów dynamicznych zapytań w SQL


Czasami zachodzi potrzeba napisania czegoś dynamicznego w SQLu. Mam tu na myśli budowaną w SQL składnie zapytania/procedury/widoku/trigera czy czegokolwiek innego. Generalnie zadanie nie jest zbyt trudne i sprowadza się do konkatenacji kilku zmiennych z ciałem tworzonego bytu i wywołaniu tego przy użyciu polecenia

[cc lang=”sql”]exec(@SQL)[/cc]

Ok, a co w przypadku, gdy z naszego zapytania chcielibyśmy zwrócić jakąś wartość do zmiennej ?

Tu z pomocą przyjdzie nam funkcja sp_executesql.

Z czym to się je ? Zademonstruję na przykładzie:

Załóżmy, że mamy taką sobie tabelkę:

addressID int
firstName nvarchar(40)
lastName nvarchar(40)
databaseName nvarchar(40)

W tabelce znajdują się dane osoby oraz nazwa bazy danych w której zawarte są jakieś dane dodatkowe. W tym miejscu czytelników proszę o nie zastanawianie się nad sensem tej tabeli. Służy ona jedynie do zademonstrowania przykładu, ale do rzeczy. Powiedzmy, że chciałbym wyciągnąć nazwę bazy danych dla zadanego id przekazać to do zmiennej i wykorzystać w innym dynamicznym zapytaniu:

[cc lang=”sql”]

DECLARE @addressID int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @dataBaseName varchar(30);

SET @addressID = 2;
SET @SQLString = N”SELECT @dataBaseNameOUT = DatabaseName
FROM test.dbo.address WHERE addressId = @ID”;
SET @ParmDefinition = N”@ID int, @dataBaseNameOUT varchar(30) OUTPUT”;

exec sp_executesql @SQLString, @ParmDefinition, @ID = @addressID, @dataBaseNameOUT = @dataBaseName OUTPUT;

SELECT @dataBaseName;

[/cc]

Co tak naprawdę robi ten kod ? Ważne są tutaj trzy rzeczy:

1. Definicja zapytania

[cc lang=”sql”]

SET @SQLString = N”SELECT @dataBaseNameOUT = DatabaseName FROM test.dbo.address WHERE addressId = @ID”;

[/cc]

Powyższy select przypisuje do zmiennej @dataBaseNameOUT nazwę bazy danych, o zadanym addressId przekazaną w zmiennej @ID.

2. Definicja wewnętrznych parametrów zapytania

[cc lang=”sql”]

SET @ParmDefinition = N”@ID int, @dataBaseNameOUT varchar(30) OUTPUT”;

[/cc]

Jak widać jest to prosta definicja zmiennych wykorzystanych w zapytaniu ze wskazaniem zmiennej zwracającej wynik na zewnątrz

3. Na koniec pozostaje nam poskładanie wszystkich parametrów w całość i przekazanie ich do funkcji sp_executesql

[cc lang=”sql”]

exec sp_executesql @SQLString, @ParmDefinition, @ID = @addressID, @dataBaseNameOUT = @dataBaseName OUTPUT;

[/cc]

Na szczególną uwagę w tym miejscu zasługuje skojarzenie parametrów zdefiniowanych w pkt. 2 z parametrami zadeklarowanymi na zewnątrz

I to by było na tyle. Na koniec zachęcam wszystkich do zapoznania się z dokumentacją procedury sp_executesql dostępną w MSDNie, gdyż treść tu zawarta nie wyczerpuje całego tematu.


Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.