Dela via

sp_describe_undeclared_parameters (Transact-SQL)

  • Artikel

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 ]' ]

Not

Om du vill använda den här lagrade proceduren i Azure Synapse Analytics i en dedikerad SQL-pool anger du databasens kompatibilitetsnivå till 20 eller högre. Om du vill avregistrera dig ändrar du databasens kompatibilitetsnivå till 10.

Argument

Viktig

Argument för utökade lagrade procedurer måste anges i den specifika ordning som beskrivs i avsnittet Syntax. Om parametrarna anges i fel ordning visas ett felmeddelande.

[ @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.

Kolumnnamn Datatyp Beskrivning
parameter_ordinal int Innehåller parameterns ordningstal i resultatuppsättningen. Positionen för den första parametern anges som 1. Inte nullbar.
name sysname Innehåller namnet på parametern. Inte nullbar.
suggested_system_type_id int Innehåller system_type_id för parameterns datatyp enligt sys.types.

För CLR-typer, även om kolumnen system_type_name returnerar NULL, returnerar den här kolumnen värdet 240. Inte nullbar.
suggested_system_type_name nvarchar(256) Innehåller namnet på datatypen. Innehåller argument (till exempel längd, precision, skala) som angetts för parameterns datatyp. Om datatypen är en användardefinierad aliastyp anges den underliggande systemtypen här. Om det är en CLR-användardefinierad datatyp returneras NULL i den här kolumnen. Om parametertypen inte kan härledas returneras NULL. Nullbar.
suggested_max_length liten Se sys.columns. för max_length kolumnbeskrivning. Inte nullbar.
suggested_precision pytteliten Se sys.columns. för beskrivning av precisionskolumner. Inte nullbar.
suggested_scale pytteliten Se sys.columns. för beskrivning av skalningskolumner. Inte nullbar.
suggested_user_type_id int För CLR- och aliastyper innehåller user_type_id av datatypen för kolumnen enligt beskrivningen i sys.types. Annars är NULL. Nullbar.
suggested_user_type_database sysname För CLR- och aliastyper innehåller namnet på databasen där typen har definierats. Annars är NULL. Nullbar.
suggested_user_type_schema sysname För CLR- och aliastyper innehåller namnet på schemat där typen har definierats. Annars är NULL. Nullbar.
suggested_user_type_name sysname För CLR- och aliastyper innehåller namnet på typen. Annars är NULL.
suggested_assembly_qualified_type_name nvarchar(4000) För CLR-typer returnerar namnet på sammansättningen och klassen som definierar typen. Annars är NULL. Nullbar.
suggested_xml_collection_id int Innehåller xml_collection_id för parameterns datatyp enligt sys.columns. Den här kolumnen returnerar NULL om den typ som returneras inte är associerad med en XML-schemasamling. Nullbar.
suggested_xml_collection_database sysname Innehåller databasen där XML-schemasamlingen som är associerad med den här typen har definierats. Den här kolumnen returnerar NULL om den typ som returneras inte är associerad med en XML-schemasamling. Nullbar.
suggested_xml_collection_schema sysname Innehåller schemat där XML-schemasamlingen som är associerad med den här typen definieras. Den här kolumnen returnerar NULL om den typ som returneras inte är associerad med en XML-schemasamling. Nullbar.
suggested_xml_collection_name sysname Innehåller namnet på XML-schemasamlingen som är associerad med den här typen. Den här kolumnen returnerar NULL om den typ som returneras inte är associerad med en XML-schemasamling. Nullbar.
suggested_is_xml_document bit Returnerar 1 om typen som returneras är XML och den typen garanterat är ett XML-dokument. Annars returneras 0. Inte nullbar.
suggested_is_case_sensitive bit Returnerar 1 om kolumnen är av skiftlägeskänslig strängtyp och 0 om den inte är det. Inte nullbar.
suggested_is_fixed_length_clr_type bit Returnerar 1 om kolumnen har en CLR-typ med fast längd och 0 om den inte är det. Inte nullbar.
suggested_is_input bit Returnerar 1 om parametern används någon annanstans än till vänster i en tilldelning. Annars returneras 0. Inte nullbar.
suggested_is_output bit Returnerar 1 om parametern används till vänster i en tilldelning eller skickas till en utdataparameter i en lagrad procedur. Annars returneras 0. Inte nullbar.
formal_parameter_name sysname Om parametern är ett argument till en lagrad procedur eller en användardefinierad funktion returnerar namnet på motsvarande formella parameter. Annars returneras NULL. Nullbar.
suggested_tds_type_id int För internt bruk. Inte nullbar.
suggested_tds_length int För internt bruk. Inte nullbar.

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.

Not

Du måste deklarera variabeln som en skalär Transact-SQL variabel, eller så visas ett fel.

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.

EXEC 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.

EXEC 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