Udostępnij za pośrednictwem

  • Artykuł

sp_executesql (języka Transact-SQL)

Wykonuje Transact-SQL Instrukcja lub partia, które mogą być używane ponownie tyle razy, czy taki, w którym został utworzony dynamicznie. The Transact-SQL instrukcja or partia can contain embedded parameters.

Security noteSecurity Note:

Uruchom skompilowany czas Transact-SQL instrukcje może narazić aplikacji do złośliwych ataków, takie jak Iniekcji SQL.

Topic link iconKonwencje składni języka Transact-SQL

sp_executesql [ @stmt = ] stmt
[ 
    {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } 
     {, [ @param1 = ] 'value1' [ ,...n ] }
]

Argumenty

  • [ @stmt = ] stmt
    Ciąg Unicode zawierający instrukcję lub partię Transact-SQL.stmtDostarczana wartość musi być stałą lub zmienną Unicode.Bardziej skomplikowane wyrażenia Unicode, takie jak łączenie dwóch ciągów za pomocą operatora +, są niedozwolone.Stałe znakowe są niedozwolone.Jeśli określono stałą Unicode, to musi być ona poprzedzona znakiem N.Na przykład stała Unicode N'sp_who' jest prawidłowa, ale stała znakowa 'sp_who' nie jest.Rozmiar ciągu jest ograniczony wyłącznie ilością dostępnej pamięci serwera bazy danych.Na serwerach 64-bitowych rozmiar ciągu jest ograniczony do 2 GB, maksymalnego rozmiaru dla typu nvarchar(max).

    Uwaga

    Jednakże należy wziąć pod uwagę następujące wskazówki:stmtN'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

    Każdy parametr zawarty w stmt musi mieć odpowiadający mu zapis na liście definicji parametrów @params oraz na liście wartości parametrów.

  • [ @params = ] **N'@**parameter_namedata_type[ ,... n ] '
    Pojedynczy ciąg zawierający definicje wszystkich parametrów osadzonych w stmt.Ciąg musi być stałą lub zmienną Unicode.Każda definicja parametru składa się z nazwy parametru i typu danychn jest symbolem zastępczym wskazującym dodatkowe definicje parametrów.Każdy parametr określony w stmt musi być zdefiniowany na liście @params.Jeśli instrukcja lub partia Transact-SQL w stmt nie zawiera parametrów, lista @params nie jest wymagana.Domyślną wartością tego parametru jest NULL.

  • [ **@**param1 = ] 'value1'
    Wartość pierwszego parametru zdefiniowana w ciągu parametru.Wartość może być stałą lub zmienną Unicode.Dla każdego parametru zawartego w stmt musi być dostarczona wartość parametru.Wartości nie są wymagane, jeśli instrukcja lub partia Transact-SQL w stmt nie zawiera parametrów.

  • Typy danych tekst i obraz
    Wskazuje, że jest to parametr wyjściowy.Parametry text, ntext i image mogą być używane jako parametry OUTPUT, chyba że jest to procedura CLR.Parametr wyjściowy używający słowa kluczowego OUTPUT może być symbolem zastępczym kursora, chyba że jest to procedura CLR.

  • n
    Sortowanie danych wyjściowychJeśli dane wyjściowe CAST lub CONVERT jest znak ciąg, a wartość wejściowa jest znakiem ciąg, dane wyjściowe ma ten sam sortowanie i etykieta sortowanie jako dane wejściowe.Jeśli dane wejściowe nie jest ciągiem znaków, dane wyjściowe ma domyślnym sortowaniem w bazie danych, a etykieta sortowanie coercible domyślnego.

Wartości kodów powrotnych

Aby przypisać różnych sortowanie danych wyjściowych, dotyczą klauzula COLLATE wynik wyrażenie funkcja CAST lub CONVERT.

Zestawy wyników

Obcinanie i zaokrąglanie wyniki

Remarks

Procedura sp_executesql działa tak samo jak polecenie EXECUTE w odniesieniu do partii, zakresu nazw i zawartości bazy danych.Instrukcja lub partia Transact-SQL w parametrze stmt procedury sp_executesql nie jest kompilowana, aż do momentu wykonania instrukcji sp_executesql.Zawartość stmt jest kompilowana i wykonywana jako plan wykonania niezależnie od planu wykonania partii, która wywołała instrukcję sp_executesql.Partia sp_executesql nie może odwoływać się do zmiennych zadeklarowanych w partii wywołującej instrukcję sp_executesql.Lokalne kursory i zmienne w partii sp_executesql nie są widoczne dla partii wywołującej instrukcję sp_executesql.Zmiany kontekstu bazy danych obowiązują tylko do zakończenia wykonania instrukcji sp_executesql.

sp_executesql może być używany zamiast procedur przechowywanych w celu wykonać Transact-SQL Instrukcja tyle razy, gdy zmiana wartości parametrów w instrukcji jest tylko zmiana. Ponieważ Transact-SQL samą deklarację pozostaje stała, natomiast tylko zmiana wartości parametru, SQL Server optymalizator kwerendy jest prawdopodobne ponownie użyć generuje przy pierwszym wykonaniu plan wykonania.

Uwaga

W poniższym przykładzie pokazano konwersji w obie strony:

sp_executesql obsługuje ustawienie wartości parametru oddzielnie od Transact-SQL ciąg znaków, jak pokazano w poniższym przykładzie.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

Parametry wyjściowe można również z sp_executesql.Poniższy przykład pobiera Tytuł zadanie z AdventureWorks.HumanResources.Employee Tabela i zwraca parametr wyjściowy @max\_title.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

Można zastępować parametry w sp_executesql ma następujące zalety do przy użyciu instrukcja wykonać do wykonywania ciąg:

  • Ponieważ tekstu Transact-SQL w instrukcji sp_executesql ciąg nie powoduje zmiany między wykonań, optymalizator kwerendy prawdopodobnie będzie pasował do Transact-SQL Instrukcja w drugim wykonaniu z plan wykonania wygenerowany dla przy pierwszym wykonaniu. Dlatego też SQL Server nie ma do kompilowania druga instrukcja.

  • Ciąg Transact-SQL jest tworzony tylko raz.

  • Parametr całkowity jest określany w swoim macierzystym formacie.Rzutowanie na Unicode nie jest wymagane.

Uprawnienia

Członkostwo w grupie wymaga publiczne roli.

Przykłady

A.Jednak nie jest spełniony dla stylów, wymienione w poniższej tabela.

Poniższy przykład tworzy i wykonuje prosty SELECT Instrukcja, która zawiera osadzony parametr o nazwie @level.

EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

B.Poniższa lista zawiera style, dla którego jest nondeterministic konwersji ciąg na datetime.

W poniższym przykładzie pokazano użycie instrukcji **sp_**executesql do wykonania ciągu budowanego dynamicznie.Procedura składowana w przykładzie jest używana do wstawienia danych do zestawu tabel wykorzystywanych do rozbicia rocznych danych sprzedażyUtworzono pojedynczą tabelę dla każdego miesiąca roku w następującym formacie:

CREATE TABLE May1998Sales
    (OrderID int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate  datetime NULL
        CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth int
        CHECK (OrderMonth = 5),
    DeliveryDate datetime  NULL,
        CHECK (DATEPART(mm, OrderDate) = OrderMonth)
    )

Ta przykładowa procedura składowana dynamicznie buduje i wykonuje instrukcję INSERT w celu wstawienia zamówień do właściwej tabeli.W tym przykładzie użyto daty zamówienia do zbudowania nazwy tabeli, która powinna zawierać dane, a następnie wykorzystano tę nazwę w instrukcji INSERT.

Uwaga

Jest to prosty przykład instrukcji sp_executesql.W tym przykładzie nie zastosowano sprawdzania błędów oraz sprawdzania reguł biznesowych, takich jak wyeliminowanie zduplikowanych numerów zamówień w różnych tabelach.

CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
                 @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
       CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
       ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
     @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
     @OrderMonth, @PrmDeliveryDate

GO

Za pomocą sp_executesql w tej procedurze jest bardziej efektywne niż przy użyciu wykonać do wykonywania ciąg znaków.Kiedy sp_executesql jest używany, dostępne są tylko 12 wersje ciąg INSERT generowane, jeden dla każdej tabela co miesiąc.ListPriceChociaż obie metody generowania ten sam numer partii, podobieństwa ciągów INSERT generowane przez sp_executesql pozwala na bardziej prawdopodobne, że optymalizator kwerendy będzie ponownego użycia planów wykonania.

C.Za pomocą CAST w celu uzyskania bardziej czytelnego tekstu

W poniższym przykładzie użyto OUTPUT parametr, aby zapisać zestaw wyników wygenerowany przez SELECT w instrukcja @SQLString parametr. Dwa SELECT instrukcje są następnie wykonywane używające wartości OUTPUT parametr.

USE AdventureWorks;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

Dodatkowe przykłady Zobacz Using sp_executesql.