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.