sp_executesql (Transact-sql)

  • Makale

Yürüten bir Transact-SQLdeyimi veya olabilir toplu birçok kez, ya da bir dinamik olarak oluşturulan yeniden. Transact-SQLDeyimini veya toplu iş katıştırılmış parametreler içerebilir.

Güvenlik notuGüvenlik Notu

Zaman derlenmiş çalıştırmak Transact-SQLdeyimleri uygulamaları kötü amaçlı saldırılara maruz.

Konu bağlantısı simgesi Transact-SQL Sözdizim Kuralları

Sözdizimi

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

Bağımsız değişkenler

  • [ @stmt= ] statement
    Bir Unicode dizesi içeren bir Transact-SQLdeyimini veya toplu iş. @stmtUnicode sabit veya Unicode değişken olmalı. İle iki dizeyi bitiştirmek gibi daha karmaşık Unicode ifadeler, + işleci, izin verilmez. Karakter sabitler izin verilmez. Bir Unicode sabiti belirtilirse, o ile öncelenmemesi gerekir bir n. Örneğin, Unicode sabit n 'sp_who' geçerlidir, ancak karakter sabiti 'sp_who' değil. Dize boyutu yalnızca kullanılabilir veritabanı sunucusu belleğiyle sınırlıdır. 64-Bit sunucularda, dize boyutu 2 gb ile maksimum boyutu sınırlıdır nvarchar(max).

    [!NOT]

    @stmt Örneğin bir değişken adı olarak aynı form sahip Parametreler içerebilir: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

    Her parametre dahil @stmtkarşılık gelen bir giriş hem de olması gerekir @paramsparametre tanımı listesi ve parametre değerleri listesi.

  • @params=N'@parameter_namedata_type,... n ] '
    İçinde katıştırılmış tüm parametreleri tanımları içeren bir dizedir @stmt. Unicode sabit ya da değişken Unicode dize olmalıdır. Her parametre tanımı, parametre adı ve veri türü oluşur. nek parametre tanımları gösteren bir yer tutucudur. Belirtilen her parametre @stmtolarak tanımlanmalıdır @params. Eğer Transact-SQLdeyimini veya toplu iş içinde @stmtparametre içermeyen @paramsgerekli değildir. Bu parametre için varsayılan değer null olur.

  • [ @param1= ] 'value1'
    Parametre dizesinde tanımlanan ilk parametre için bir değer. Değer, bir Unicode sabit veya Unicode değişken olabilir. Orada-meli var olmak dahil her parametre için verilen parametre değeri @stmt. Değerleri ne zaman gerekli değildir Transact-SQLdeyimini veya toplu iş içinde @stmtparametre vardır.

  • [OUT | ÇIKIŞ]
    Parametre bir çıktı parametresi gösterir. text, ntext, ve imageparametreleri bir ortak dil çalışma zamanı (clr) yordamı yordam olmadığı sürece çıkış parametreleri kullanılabilir. clr yordam yordam olmadığı sürece bir çıkış parametresi çıkış anahtar kullanan bir imleç yer tutucu olabilir.

  • n
    Ek parametre değerleri için bir yer tutucudur. Değerler, yalnızca sabit ya da değişken olabilir. Değerleri fonksiyonları gibi daha karmaşık ifadeler veya işleçleri kullanarak yerleşik ifadeler olamaz.

Dönüş Kodu Değerleri

0 (başarılı) veya sıfır olmayan (başarısız)

Sonuç Kümeleri

sql dizesine yerleşik tüm sql deyimlerinden sonuç kümeleri döndürür.

Açıklamalar

sp_executesqlParametreler, "Sözdizimi" açıklandığı gibi belirli düzende girilmelidir bölümünde bu konuda. Parametre sıra dışı olarak girilirse, hata iletisi oluşacaktır.

sp_executesqlYÜRÜTME toplu işlemleri, adları ve veritabanı içerik kapsamı konusunda aynı davranışı vardır. Transact-SQLDeyimini veya toplu iş içinde sp_executesql  @stmtparametresi değil kadar derlenmiş sp_executesqldeyimi yürütüldüğünde. İçeriğini @stmtsonra derlenen ve yürütme planı bu adı verilen toplu iş yürütme planından ayrı olarak idam sp_executesql. sp_executesql, Çağıran toplu işleme bildirilen değişkenler toplu başvuru yapamazsınız sp_executesql. Yerel imleçler ya da değişkenlerin sp_executesqltoplu görünür değil çağırır, toplu sp_executesql. Veritabanı bağlamında son değişiklikleri yalnızca sonuna kadar sp_executesqldeyimi.

sp_executesqlyerine saklı yordamları yürütmek için kullanılabilir bir Transact-SQLdeyimi defalarca deyimi parametre değerlerini değiştirmek yalnızca çeşididir. Çünkü Transact-SQLdeyimi kendisi kalır sabit ve yalnızca parametre değerlerini değiştirmek, SQL Serversorgu iyileştirici büyük bir olasılıkla ilk yürütülmesi için oluşturduğu yürütme planı yeniden.

[!NOT]

Performans kullanımı tamamen iyileştirmek için nesne adlarında deyimi dizesi nitelenmiş.

sp_executesqlparametre değerleri ayrı ayrı ayarını destekleyen Transact-SQLdize aşağıdaki örnekte gösterildiği gibi.

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

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

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

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

Çıkış parametreleri de kullanılabilir olan sp_executesql. Aşağıdaki örnek bir unvan den alır AdventureWorks2012.HumanResources.Employeetablo ve çıktı parametresinde döndürür @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(JobTitle) 
   FROM AdventureWorks2012.HumanResources.Employee
   WHERE BusinessEntityID = @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;

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(JobTitle) 
   FROM AdventureWorks2012.HumanResources.Employee
   WHERE BusinessEntityID = @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;

Parametreleri yerine mümkün olan sp_executesqlbir dize çalıştırmak için execute deyimi kullanmak için aşağıdaki avantajları sunar:

  • Çünkü gerçek metnini de Transact-SQLdeyimi sp_executesqldize infaz arasında değiştirmez, query optimizer muhtemelen maç Transact-SQLilk yürütülmesi için oluşturulan yürütme planı ile ikinci yürütme deyimini. Bu nedenle, SQL Serverikinci deyimi derlemek zorunda değildir.

  • Transact-SQLDize yalnızca bir kez üretilmiştir.

  • Tamsayı parametre kendi özgün biçiminde belirtilir. Döküm Unicode için gerekli değildir.

İzinler

Üyelik publicrolü.

Örnekler

A.Bir select deyimi yürütme

Aşağıdaki örnek oluşturur ve basit yürütür SELECTadlı bir katıştırılmış parametresi içeren deyimi @level.

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

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

B.Dinamik olarak oluşturulmuş bir dize yürütme

Aşağıdaki örnek kullanarak sp_executesqldinamik olarak oluşturulmuş bir dize yürütülecek. Örnek saklı yordam veri bir yıl için satış verilerini bölümlemek için kullanılan tablolar kümesini eklemek için kullanılır. Aşağıdaki biçimi olan yılın her ayı için bir tablo şöyledir:

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)
    )

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)
    )

Bu örnek saklı yordam dinamik olarak oluşturulur ve yürütülür bir INSERTYeni siparişleri doğru tabloya eklemek için açıklama. Örnek verileri içermesi ve bu adı halinde birleştirmek tablo adını oluşturmak için sipariş tarihini kullanır bir INSERTdeyimi.

[!NOT]

Bunun için basit bir örnek ise sp_executesql. Örnek hata denetimi içermiyor ve sipariş numaralarını tablolar arasında çoğaltılır değil garanti gibi iş kurallarını denetler içermez.

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

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

Kullanarak sp_executesqlBu yordamda daha bir dize çalıştırmak için execute kullanarak daha verimli olur. Ne zaman sp_executesqlise kullanılan, sadece 12 sürümleri vardır oluşturulan, ekleme dizesi bir aylık her tablo için. execute ile her ekleme dizesi parametresi değerleri farklı olduğundan benzersizdir. INSERT dizeleri benzerlik olmasına rağmen her iki yöntem de aynı sayıda toplu işlemleri oluşturmak, oluşturulan tarafından sp_executesqldaha büyük olasılıkla sorgu iyileştirici yürütme planları yeniden yapar.

C.Çıkış parametresi kullanma

Aşağıdaki örnek bir OUTPUTparametresi tarafından oluşturulan sonuç kümesini depolamak için SELECTdeyimi @SQLStringparametresi. İki SELECTdeyimleri sonra yürütülür değerini kullanan OUTPUTparametresi.

USE AdventureWorks2012;
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;

USE AdventureWorks2012;
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;

Ayrıca bkz.

Başvuru

execute (Transact-sql)

Sistem saklı yordamları (Transact-sql)