sp_describe_undeclared_parameters (Transact-SQL)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft Fabric

Returnerar en resultatuppsättning som innehåller metadata om odeklarerade parametrar i en Transact-SQL batch. Tar hänsyn till varje parameter som används i batchen @tsql, men som inte deklareras i @params. En resultatuppsättning returneras som innehåller en rad för varje sådan parameter, med den härledda typinformationen för parametern. Proceduren returnerar en tom resultatuppsättning om den @tsql indatabatchen inte har några parametrar förutom de som deklareras i @params.

Transact-SQL syntaxkonventioner

Syntax

sp_describe_undeclared_parameters
    [ @tsql = ] 'Transact-SQL_batch'
    [ , [ @params = ] N'@parameter_name data_type [ , ... n ]' ]

Argument

[ @tsql = ] "tsql"

En eller flera Transact-SQL-instruktioner. @tsql kan vara nvarchar(n) eller nvarchar(max).

[ @params = ] N'@parameter_namedata_type [ ,... n ]'

@params innehåller en deklarationssträng för parametrar för Transact-SQL batch, på samma sätt som sp_executesql fungerar. @params kan vara nvarchar(n) eller nvarchar(max).

En sträng som innehåller definitionerna för alla parametrar som är inbäddade i @tsql. Strängen måste vara antingen en Unicode-konstant eller en Unicode-variabel. Varje parameterdefinition består av ett parameternamn och en datatyp. n är en platshållare som anger ytterligare parameterdefinitioner. Om Transact-SQL-instruktionen eller batchen i -instruktionen inte innehåller parametrar krävs inte @params. Standardvärdet för den här parametern är NULL.

Returnera kodvärden

sp_describe_undeclared_parameters returnerar alltid statusen noll vid lyckat resultat. Om proceduren utlöser ett fel och proceduren anropas som en RPC fylls returstatusen i av typen av fel enligt beskrivningen i kolumnen error_type i sys.dm_exec_describe_first_result_set. Om proceduren anropas från Transact-SQL är returvärdet alltid noll, även i felfall.

Resultatuppsättning

sp_describe_undeclared_parameters returnerar följande resultatuppsättning.

Anmärkningar

sp_describe_undeclared_parameters returnerar alltid statusen noll.

Den vanligaste användningen är när ett program får en Transact-SQL-instruktion som kan innehålla parametrar och måste bearbeta dem på något sätt. Ett exempel är ett användargränssnitt (till exempel ODBCTest eller RowsetViewer) där användaren tillhandahåller en fråga med ODBC-parametersyntax. Programmet måste dynamiskt identifiera antalet parametrar och fråga användaren om var och en.

Ett annat exempel är när ett program utan användarindata måste loopa över parametrarna och hämta data för dem från någon annan plats (till exempel en tabell). I det här fallet behöver programmet inte skicka all parameterinformation samtidigt. I stället kan programmet hämta all parameterinformation från providern och hämta själva data från tabellen. Kod som använder sp_describe_undeclared_parameters är mer allmän och är mindre sannolikt att kräva ändringar om datastrukturen ändras senare.

sp_describe_undeclared_parameters returnerar ett fel i något av följande fall.

• Indata @tsql är inte en giltig Transact-SQL batch. Giltigheten bestäms genom parsning och analys av Transact-SQL batch. Eventuella fel som orsakas av batchen under frågeoptimeringen eller under körningen beaktas inte när du avgör om Transact-SQL batchen är giltig.

• @params är inte NULL och innehåller en sträng som inte är en syntaktiskt giltig deklarationssträng för parametrar, eller om den innehåller en sträng som deklarerar en parameter mer än en gång.

• Indata Transact-SQL batch deklarerar en lokal variabel med samma namn som en parameter som deklareras i @params.

• Instruktionen refererar till temporära tabeller.

• Frågan innehåller skapandet av en permanent tabell som sedan efterfrågas.

Om @tsql inte har några parametrar, förutom parametrar som deklarerats i @params, returnerar proceduren en tom resultatuppsättning.

Algoritm för val av parameter

För en fråga med odeklarerade parametrar fortsätter datatypsavdraget för odeklarerade parametrar i tre steg.

Steg 1: Hitta datatyperna för underuttrycken

Det första steget i datatypsavdrag för en fråga med odeklarerade parametrar är att hitta datatyperna för alla underuttryck vars datatyper inte är beroende av de odeklarerade parametrarna. Typen kan fastställas för följande uttryck:

• Kolumner, konstanter, variabler och deklarerade parametrar.
• Resultat av ett anrop till en användardefinierad funktion (UDF).
• Ett uttryck med datatyper som inte är beroende av de odeklarerade parametrarna för alla indata.

Tänk till exempel på frågan SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. Uttrycken dbo.tbl(@p1) + c1 och c2 har datatyper och uttryck @p1 och @p2 + 2 inte.

Efter det här steget, om något uttryck (förutom ett anrop till en UDF) har två argument utan datatyper, misslyckas typavdraget med ett fel. Följande genererar till exempel alla fel:

SELECT * FROM t1 WHERE @p1 = @p2;
SELECT * FROM t1 WHERE c1 = @p1 + @p2;
SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3);

Följande exempel genererar inget fel:

SELECT * FROM t1 WHERE @p1 = dbo.tbl(c1, @p2, @p3);

Steg 2: Hitta de innersta uttrycken

För en viss odeklarerad parameter @phittar typavdragsalgoritmen det innersta uttrycket E(@p) som innehåller @p och är ett av följande argument:

• Ett argument till en jämförelse- eller tilldelningsoperator.
• Ett argument till en användardefinierad funktion (inklusive tabellvärdes-UDF), procedur eller metod.
• Ett argument till en VALUES-sats i en INSERT-instruktion.
• Ett argument till en CAST eller CONVERT.

Typavdragsalgoritmen hittar en måldatatyp TT(@p) för E(@p). Måldatatyper för föregående exempel är följande:

• Datatypen för den andra sidan av jämförelsen eller tilldelningen.
• Den deklarerade datatypen för parametern som det här argumentet skickas till.
• Datatypen för kolumnen som det här värdet infogas i.
• Den datatyp som -instruktionen konverterar till.

Tänk till exempel på frågan SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). Sedan är E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) den deklarerade returdatatypen för dbo.tbloch TT(@p2) är den deklarerade parameterdatatypen för dbo.tbl.

Om @p inte finns i något uttryck som anges i början av steg 2 avgör typavdragsalgoritmen att E(@p) är det största skalära uttrycket som innehåller @p, och typavdragsalgoritmen beräknar inte en måldatatyp TT(@p) för E(@p). Om frågan till exempel är SELECT @p + 2E(@p) = @p + 2och det inte finns någon TT(@p).

Steg 3: Härled datatyper

Nu när E(@p) och TT(@p) har identifierats härleds typavdragsalgoritmen en datatyp för @p på något av följande två sätt:

• Enkelt avdrag

  Om E(@p) = @p och TT(@p) finns, dvs. om @p är direkt ett argument till ett av de uttryck som anges i början av steg 2, så härleder typavdragsalgoritmen datatypen för @p som ska TT(@p). Till exempel:

  SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);
  

  Datatypen för @p1, @p2och @p3 är datatypen för c1, returdatatypen för dbo.tblrespektive parameterdatatypen för dbo.tbl.

  Om @p är ett argument för en <, >, <=eller >= operator gäller inte enkla avdragsregler. Typavdragsalgoritmen använder de allmänna avdragsregler som beskrivs i nästa avsnitt. Om c1 till exempel är en kolumn av datatyp tecken(30)bör du överväga följande två frågor:

  SELECT * FROM t WHERE c1 = @p;
  SELECT * FROM t WHERE c1 > @p;
  

  I det första fallet härleds algoritmen för typavdrag tecken(30) som datatyp för @p enligt regler tidigare i den här artikeln. I det andra fallet drar typavdragsalgoritmen varchar(8000) enligt de allmänna avdragsreglerna i nästa avsnitt.

• Allmänt avdrag

  Om enkelt avdrag inte gäller beaktas följande datatyper för odeklarerade parametrar:

  • Heltalsdatatyper (bit, tinyint, smallint, int, bigint)

  • Datatyper för pengar (smallmoney, pengar)

  • Flyttalsdatatyper (flyttal, verkliga)

  • numeriska (38, 19) – Andra numeriska datatyper eller decimaldatatyper beaktas inte.

  • varchar(8000), varchar(max), nvarchar(4000)och nvarchar(max) – Andra strängdatatyper (till exempel text, char(8000), nvarchar(30)osv.) beaktas inte.

  • varbinary(8000) och varbinary(max) – Andra binära datatyper beaktas inte (till exempel bild, binary(8000), varbinary(30)osv.).

  • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) – Andra datum- och tidstyper, till exempel tid(4), beaktas inte.

  • sql_variant

  • XML-

  • CLR-systemdefinierade typer (hierarchyid, geometri, geografi)

  • ANVÄNDARDEFINIERADE CLR-typer

Urvalskriterier

Av kandidatdatatyperna avvisas alla datatyper som skulle ogiltigförklara frågan. Av de återstående kandidatdatatyperna väljer typavdragsalgoritmen en enligt följande regler.

1. Den datatyp som ger det minsta antalet implicita konverteringar i E(@p) väljs. Om en viss datatyp genererar en datatyp för E(@p) som skiljer sig från TT(@p)anser typavdragsalgoritmen att detta är en extra implicit konvertering från datatypen E(@p) till TT(@p).

   Till exempel:

   SELECT * FROM t WHERE Col_Int = Col_Int + @p;
   

   I det här fallet är E(@p)Col_Int + @p och TT(@p) är int. int väljs för @p eftersom det inte ger några implicita konverteringar. Alla andra val av datatyp ger minst en implicit konvertering.

2. Om flera datatyper är kopplade till det minsta antalet konverteringar används datatypen med högre prioritet. Till exempel:

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
   

   I det här fallet skapar int och smallint en konvertering. Alla andra datatyper genererar mer än en konvertering. Eftersom int har företräde framför smallintanvänds int för @p. Mer information om prioritet för datatyper finns i Prioritet för datatyp.

   Den här regeln gäller endast om det finns en implicit konvertering mellan varje datatyp som kopplas enligt regel 1 och datatypen med störst prioritet. Om det inte finns någon implicit konvertering misslyckas datatypsavdraget med ett fel. I frågan SELECT @p FROM tmisslyckas till exempel datatypsavdraget eftersom alla datatyper för @p skulle vara lika bra. Det finns till exempel ingen implicit konvertering från int till XML-.

3. Om två liknande datatyper kopplas under regel 1, till exempel varchar(8000) och varchar(max), väljs den mindre datatypen (varchar(8000)). Samma princip gäller för nvarchar- och varbinära datatyper.

4. I regel 1 föredrar typavdragsalgoritmen vissa konverteringar som bättre än andra. Konverteringar i ordning från bästa till sämsta är:

   1. Konvertering mellan samma grundläggande datatyp med olika längd.
   2. Konvertering mellan version med fast längd och variabel längd av samma datatyper (till exempel tecken till varchar).
   3. Konvertering mellan NULL och int.
   4. Eventuell annan konvertering.

För frågan SELECT * FROM t WHERE [Col_varchar(30)] > @pväljs varchar(8000) eftersom konvertering (a) är bäst. För frågan SELECT * FROM t WHERE [Col_char(30)] > @pväljs varchar(8000) fortfarande eftersom den orsakar en typkonvertering (b) och eftersom ett annat val (till exempel varchar(4000)) skulle orsaka en typ (d) konvertering.

Som ett sista exempel, med tanke på en fråga SELECT NULL + @p, väljs int för @p eftersom det resulterar i en typkonvertering (c).

Behörigheter

Kräver behörighet att köra argumentet @tsql.

Exempel

I följande exempel returneras information som den förväntade datatypen för de odeklarerade parametrarna @id och @name.

EXECUTE sp_describe_undeclared_parameters @tsql = N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR name = @name';

När parametern @id anges som en @params referens utelämnas parametern @id från resultatuppsättningen och endast parametern @name beskrivs.

EXECUTE sp_describe_undeclared_parameters @tsql = N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name', @params = N'@id int';

Relaterat innehåll

• sp_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)