By: Greg Robidoux

przegląd

rzeczywistą mocą procedur składowanych jest możliwość przekazywania parametrów i havethe procedura składowana obsługuje różne żądania, które są wykonane. W thistopic przyjrzymy się przekazywaniu wartości parametrów do procedury składowanej.

Wyjaśnienie

podobnie jak masz możliwość używania parametrów z kodem SQL możesz również ustawić swoje procedury składowane, aby akceptowały jedną lub więcej wartości parametrów. Allexamp korzysta z bazy danych AdventureWorks.,

Tworzenie procedury składowanej SQL z parametrami

1. aby utworzyć procedurę składowaną z parametrami przy użyciu następującej składni:
2. tworzenie procedury dbo.uspGetAddress @ City nvarchar (30) as
3. zobacz szczegóły i przykłady poniżej

zapytanie SQL Server do przekształcenia procedury składowanej

poniżej znajduje się zapytanie, którego chcemy użyć do utworzenia procedury składowanej.

USE AdventureWorksGOSELECT * FROM Person.AddressGO

chodzi o to, aby utworzyć procedurę składowaną, w której miasto jest przekazywane do przechowywanej procedury, aby móc tworzyć dynamiczne wyniki., Można to zrobić w następujący sposób przy użyciu zmiennej. Jeśli uruchomimy poniższy kod zwróci tylko wyniki dla NewYork.

USE AdventureWorksGODECLARE @City nvarchar(30)SET @City = 'New York'SELECT * FROM Person.AddressWHERE City = @CityGO

moglibyśmy użyć tego podejścia i nadal aktualizować zmienną @City, ale jest lepszy sposób, aby to zrobić, tworząc procedurę składowaną.

Utwórz procedurę składowaną SQL Server z jednym parametrem

w tym przykładzie zapytamy osobę.Tabela adresów z bazy AdventureWorksdatabase, ale zamiast odzyskać wszystkie rekordy, ograniczymy ją tylko do konkretnej wartości., Ten przykład zakłada, że będzie dokładne dopasowanie wartości miasta, które zostanie przekazane.

USE AdventureWorksGOCREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)ASSELECT * FROM Person.AddressWHERE City = @CityGO

aby wywołać tę procedurę składowaną, wykonamy ją w następujący sposób:

EXEC dbo.uspGetAddress @City = 'New York'

usunięcie procedury składowanej

jeśli utworzyłeś procedurę składowaną i chcesz odtworzyć procedurę składowaną o tej samej nazwie, możesz można go usunąć, korzystając z poniższegoprzed próbą utworzenia go ponownie.

USE AdventureWorksGODROP PROCEDURE dbo.uspGetAddressGO

Jeśli spróbujesz utworzyć procedurę składowaną, która już istnieje, otrzymasz komunikat o błędzie.,

procedura składowana SQL Server z parametrem za pomocą symboli wieloznacznych

możemy również zrobić to samo, ale pozwolić użytkownikom dać nam punkt początkowy do przeszukiwania danych.

tutaj możemy zmienić „=” na LIKE i użyć „%” wildcard.

można to uruchomić w następujący sposób, aby znaleźć wszystkie miasta, które zaczynają się od Nowego.,

EXEC dbo.uspGetAddress @City = 'New'

błąd procedury składowanej SQL Server, Gdy parametr nie został przekazany

w obu postępujących przykładach zakłada się, że wartość parametru zawsze zostanie przekazana. Jeśli spróbujesz wykonać procedurę bez podania parametervalue, otrzymasz komunikat o błędzie, taki jak:

EXEC dbo.uspGetAddress

procedura składowana SQL Server z użyciem NULL jako domyślnego parametru

w większości przypadków zawsze dobrą praktyką jest przekazywanie wszystkich wartości parametrów,ale czasami nie jest to możliwe. Tak więc w tym przykładzie używamy opcji NULL, aby pozwolić Ci nie przekazać wartości parametru. Jeśli utworzymy i uruchomimy ten proces storedprocedure tak jak jest, nie zwróci żadnych danych, ponieważ szuka żadnych wartości Cityvalues, które są równe NULL.

USE AdventureWorksGOCREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULLASSELECT *FROM Person.AddressWHERE City = @CityGO

jeśli wykonamy następujące czynności To będzie działać, ale żadne dane nie zostaną zwrócone.,

EXEC dbo.uspGetAddress

możemy zmienić tę procedurę składowaną i użyć funkcji ISNULL, aby uzyskać aroundthis. Więc jeśli wartość jest przekazywana, użyje jej do zawężenia zestawu wyników, a jeśli wartość nie zostanie przekazana, zwróci wszystkie rekordy. (Uwaga: jeśli kolumna City ma wartości NULL, nie będzie ona zawierać tych wartości. Będziesz musiał dodać dodatkową logikę dla City IS NULL)

teraz jeśli uruchomimy poniższe polecenie, Wszystkie dane zostaną zwrócone z tabeli.,

EXEC dbo.uspGetAddress

Utwórz procedurę składowaną SQL Server z wieloma parametrami

ustawienie wielu parametrów jest bardzo proste. Wystarczy wylistować każdy parametr i typ danych oddzielony przecinkiem, jak pokazano poniżej.

aby to wykonać, możesz wykonać jedną z następujących czynności:

Ostatnia aktualizacja: 4/3/2020