sp_describe_undeclared_parameters (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL Analytics-eindpunt in Microsoft FabricMagazijn in Microsoft FabricSQL-database in Microsoft Fabric

Geeft een resultaatset terug die metadata bevat over niet-gedeclareerde parameters in een Transact-SQL batch. Beschouwt elke parameter die in de @tsql batch wordt gebruikt, maar niet in @params wordt gedeclareerd. Er wordt een resultaatset teruggegeven die één rij bevat voor elke dergelijke parameter, met de afgeleide type-informatie voor die parameter. De procedure geeft een lege resultaatset terug als de @tsql invoerbatch geen parameters heeft behalve die welke in @params zijn gedeclareerd.

Transact-SQL syntaxis-conventies

Syntaxis

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

Opmerking

Om deze opgeslagen procedure in Azure Synapse Analytics in een speciale SQL-pool te gebruiken, stel je het compatibiliteitsniveau van de database op 20 of hoger in. Om af te melden, verander je het databasecompatibiliteitsniveau naar 10.

Arguments

Belangrijk

Argumenten voor uitgebreide opgeslagen procedures moeten worden ingevoerd in de specifieke volgorde, zoals beschreven in de sectie Syntaxis. Als de parameters niet in de volgorde zijn ingevoerd, treedt er een foutbericht op.

[ @tsql = ] 'tsql-'

Een of meer Transact-SQL instructies. @tsql kan nvarchar(n) of nvarchar(max)zijn.

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

@params levert een declaratiestring voor parameters voor de Transact-SQL batch, vergelijkbaar met de manier sp_executesql waarop het werkt. @params zou nvarchar(n) of nvarchar(max) kunnen zijn.

Een string die de definities bevat van alle parameters die in @tsql zijn ingebed. De tekenreeks moet een Unicode-constante of een Unicode-variabele zijn. Elke parameterdefinitie bestaat uit een parameternaam en een gegevenstype. n is een tijdelijke aanduiding die aanvullende parameterdefinities aangeeft. Als de Transact-SQL-instructie of batch in de instructie geen parameters bevat, is @params niet vereist. De standaardwaarde voor deze parameter is NULL.

Codewaarden retourneren

sp_describe_undeclared_parameters Geeft altijd de status nul terug bij succes. Als de procedure een fout geeft en de procedure wordt een RPC genoemd, wordt de retourstatus gevuld met het type fout zoals beschreven in de error_type kolom van sys.dm_exec_describe_first_result_set. Als de procedure wordt aangeroepen vanuit Transact-SQL, is de retourwaarde altijd nul, zelfs in foutgevallen.

Resultaatset

sp_describe_undeclared_parameters retourneert de volgende resultatenset.

Kolomnaam	Gegevenstype	Description
parameter_ordinal	int	Bevat de ordinaalpositie van de parameter in de resultaatverzameling. De positie van de eerste parameter wordt gespecificeerd als 1. Niet nullwaarde.
name	sysname	Bevat de naam van de parameter. Niet nullwaarde.
suggested_system_type_id	int	Bevat de system_type_id van het datatype van de parameter zoals gespecificeerd in sys.types. Voor CLR-typen, hoewel de system_type_name kolom teruggeeft NULL, geeft deze kolom de waarde 240. Niet nullwaarde.
suggested_system_type_name	nvarchar(256)	Bevat de naam van het datatype. Bevat argumenten (zoals lengte, precisie, schaal) die zijn gespecificeerd voor het datatype van de parameter. Als het datatype een door de gebruiker gedefinieerde alias is, wordt het onderliggende systeemtype hier gespecificeerd. Als het een door de gebruiker gedefinieerde CLR-datatype is, NULL wordt het in deze kolom teruggegeven. Als het type van de parameter niet kan worden afgeleid, NULL wordt teruggegeven. Nullable.
suggested_max_length	smallint	Zie sys.columns. max_length voor kolombeschrijving. Niet nullwaarde.
suggested_precision	tinyint	Zie sys.columns. voor precieze kolombeschrijving. Niet nullwaarde.
suggested_scale	tinyint	Zie sys.columns. voor beschrijving van de schaalkolom. Niet nullwaarde.
suggested_user_type_id	int	Voor CLR- en aliastypes bevat de user_type_id van het datatype van de kolom zoals gespecificeerd in sys.types. Anders is NULL. Nullable.
suggested_user_type_database	sysname	Voor CLR- en aliastypes bevat het de naam van de database waarin het type is gedefinieerd. Anders is NULL. Nullable.
suggested_user_type_schema	sysname	Voor CLR- en aliastypes bevat het de naam van het schema waarin het type is gedefinieerd. Anders is NULL. Nullable.
suggested_user_type_name	sysname	Voor CLR- en aliastypes bevat de naam van het type. Anders is NULL.
suggested_assembly_qualified_type_name	nvarchar(4000)	Voor CLR-types geeft het de naam van de assembly en klasse die het type definieert, terug. Anders is NULL. Nullable.
suggested_xml_collection_id	int	Bevat de xml_collection_id van het datatype van de parameter zoals gespecificeerd in sys.columns. Deze kolom geeft terug NULL als het geretourneerde type niet gekoppeld is aan een XML-schemacollectie. Nullable.
suggested_xml_collection_database	sysname	Bevat de database waarin de XML-schemacollectie die aan dit type is gekoppeld is gedefinieerd. Deze kolom geeft terug NULL als het geretourneerde type niet gekoppeld is aan een XML-schemacollectie. Nullable.
suggested_xml_collection_schema	sysname	Bevat het schema waarin de XML-schemacollectie die aan dit type is gekoppeld is gedefinieerd. Deze kolom geeft terug NULL als het geretourneerde type niet gekoppeld is aan een XML-schemacollectie. Nullable.
suggested_xml_collection_name	sysname	Bevat de naam van de XML-schemacollectie die aan dit type is gekoppeld. Deze kolom geeft terug NULL als het geretourneerde type niet gekoppeld is aan een XML-schemacollectie. Nullable.
suggested_is_xml_document	bit	Geeft terug 1 als het type dat wordt teruggegeven XML is en dat type gegarandeerd een XML-document is. Anders wordt 0geretourneerd. Niet nullwaarde.
suggested_is_case_sensitive	bit	Geeft terug 1 als de kolom van een hoofdlettergevoelig stringtype is en 0 als dat niet zo is. Niet nullwaarde.
suggested_is_fixed_length_clr_type	bit	Geeft terug 1 als de kolom van een CLR-type met vaste lengte is en 0 als dat niet zo is. Niet nullwaarde.
suggested_is_input	bit	Geeft terug 1 als de parameter ergens anders wordt gebruikt dan aan de linkerkant van een toewijzing. Anders wordt 0geretourneerd. Niet nullwaarde.
suggested_is_output	bit	Retournert 1 als de parameter aan de linkerkant van een toewijzing wordt gebruikt of wordt doorgegeven aan een uitvoerparameter van een opgeslagen procedure. Anders wordt 0geretourneerd. Niet nullwaarde.
formal_parameter_name	sysname	Als de parameter een argument is voor een opgeslagen procedure of een door de gebruiker gedefinieerde functie, geeft de naam van de bijbehorende formele parameter terug. Anders wordt NULLgeretourneerd. Nullable.
suggested_tds_type_id	int	Voor intern gebruik. Niet nullwaarde.
suggested_tds_length	int	Voor intern gebruik. Niet nullwaarde.

Opmerkingen

sp_describe_undeclared_parameters geeft altijd status nul terug.

Het meest voorkomende gebruik is wanneer een applicatie een Transact-SQL-instructie krijgt die parameters kan bevatten en deze op een bepaalde manier moet verwerken. Een voorbeeld is een gebruikersinterface (zoals ODBCTest of RowsetViewer) waarbij de gebruiker een query levert met ODBC-parametersyntaxis. De applicatie moet dynamisch het aantal parameters ontdekken en de gebruiker voor elke parameter opgeven.

Een ander voorbeeld is wanneer een applicatie zonder gebruikersinvoer de parameters moet herhalen en de gegevens van een andere locatie (zoals een tabel) moet halen. In dit geval hoeft de applicatie niet alle parameterinformatie tegelijk door te geven. In plaats daarvan kan de applicatie alle parameterinformatie van de provider verkrijgen en de data zelf uit de tabel halen. Het gebruik sp_describe_undeclared_parameters van code is generieker en is minder waarschijnlijk dat er aanpassing nodig is als de datastructuur later verandert.

sp_describe_undeclared_parameters geeft een foutmelding in een van de volgende gevallen.

• De invoer @tsql is geen geldige Transact-SQL batch. De validiteit wordt bepaald door het parsen en analyseren van de Transact-SQL batch. Eventuele fouten veroorzaakt door de batch tijdens queryoptimalisatie of uitvoering worden niet meegenomen bij het bepalen of de Transact-SQL batch geldig is.

• @params is niet NULL en bevat een string die geen syntactisch geldige declaratiestring voor parameters is, of als het een string bevat die elke parameter meer dan één keer declareert.

• De invoer Transact-SQL batch declareert een lokale variabele met dezelfde naam als een parameter die in @params is gedeclareerd.

• De verklaring verwijst naar tijdelijke tabellen.

• De query omvat het aanmaken van een permanente tabel die vervolgens wordt bevraagd.

Als @tsql geen parameters heeft, behalve parameters die in @params zijn gedeclareerd, geeft de procedure een lege resultaatset terug.

Opmerking

Je moet de variabele declareren als een scalair Transact-SQL variabele, anders verschijnt er een fout.

Parameterselectie-algoritme

Voor een zoekopdracht met niet-gedeclareerde parameters verloopt de datatype-deductie voor niet-gedeclareerde parameters in drie stappen.

Stap 1: Vind de datatypes van de subexpressies

De eerste stap bij het deducteren van datatypes voor een query met niet-gedeclareerde parameters is het vinden van de datatypes van alle subexpressies waarvan de datatypes niet afhankelijk zijn van de niet-gedeclareerde parameters. Het type kan worden bepaald voor de volgende uitdrukkingen:

• Kolommen, constanten, variabelen en gedeclareerde parameters.
• Resultaten van een aanroep naar een door de gebruiker gedefinieerde functie (UDF).
• Een expressie met datatypes die niet afhankelijk zijn van de niet-gedeclareerde parameters voor alle invoer.

Beschouw bijvoorbeeld de query SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. De expressies dbo.tbl(@p1) + c1 en c2 hebben datatypes, en expressie @p1 en @p2 + 2 niet.

Na deze stap, als een expressie (behalve een aanroep naar een UDF) twee argumenten zonder datatypes heeft, faalt type-deductie met een fout. Bijvoorbeeld, de volgende veroorzaken allemaal fouten:

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

Het volgende voorbeeld levert geen fout op:

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

Stap 2: Vind de binnenste uitdrukkingen

Voor een gegeven niet-gedeclareerde parameter @pvindt het type-deductie-algoritme de binnenste uitdrukking E(@p) die bevat @p en een van de volgende argumenten is:

• Een argument voor een vergelijkings- of toewijzingsoperator.
• Een argument voor een door de gebruiker gedefinieerde functie (inclusief tabelwaarde UDF), procedure of methode.
• Een argument tegen een VALUES clausule van een INSERT uitspraak.
• Een argument voor een CAST of CONVERT.

Het type-deductie-algoritme vindt een doeldatatype TT(@p) voor E(@p). Doeldatatypes voor de voorgaande voorbeelden zijn als volgt:

• Het datatype van de andere kant van de vergelijking of toewijzing.
• Het gedeclareerde datatype van de parameter waaraan dit argument wordt doorgegeven.
• Het datatype van de kolom waarin deze waarde wordt ingevoegd.
• Het datatype waarnaar de verklaring wordt gecast of omgezet.

Beschouw bijvoorbeeld de query SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). Dan E(@p1) = @p1is , E(@p2) = @p2 + c1, TT(@p1) het gedeclareerde retourdatatype van dbo.tbl, en TT(@p2) het gedeclareerde parameterdatatype voor dbo.tbl.

Als @p niet is opgenomen in een expressie die aan het begin van stap 2 wordt vermeld, bepaalt het type-deductiealgoritme dat E(@p) de grootste scalair-expressie is die bevat @p, en berekent het type-deductie-algoritme geen doel-datatype TT(@p) voor E(@p). Bijvoorbeeld, als de query is SELECT @p + 2 , dan E(@p) = @p + 2, en er is geen TT(@p).

Stap 3: Leid datatypes af

Nu en E(@p)TT(@p) zijn geïdentificeerd, leidt het type-deductie-algoritme een datatype voor af @p op een van de volgende twee manieren:

• Eenvoudige deductie

  Als E(@p) = @p en TT(@p) bestaat, dat wil zeggen, als @p direct een argument is voor een van de expressies die aan het begin van stap 2 zijn vermeld, leidt het type-deductiealgoritme het datatype van @p af als TT(@p). Voorbeeld:

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

  Het datatype voor @p1, , en @p3 zal respectievelijk het datatype van c1, het retourdatatype van dbo.tbl, en het parameterdatatype voor dbo.tbl zijn. @p2

  Als speciaal geval, als @p een argument is voor een <, >, <=, of >= operator, zijn eenvoudige deductieregels niet van toepassing. Het type-deductiealgoritme zal de algemene deductieregels gebruiken die in de volgende sectie worden uitgelegd. Als c1 bijvoorbeeld een kolom is van het datatype char(30), beschouw dan de volgende twee zoekopdrachten:

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

  In het eerste geval leidt het type-deductiealgoritme char(30) af als het datatype voor @p volgens de regels eerder in dit artikel. In het tweede geval leidt het type-deductiealgoritme varchar(8000) af volgens de algemene deductieregels in de volgende sectie.

• Algemene deductie

  Als eenvoudige deductie niet van toepassing is, worden de volgende datatypen beschouwd voor niet-gedeclareerde parameters:

  • Integer datatypes (bit, tinyint, smallint, int, bigint)

  • Gelddatatypes (kleingeld,geld)

  • Floating-point datatypes (floating, reël)

  • numeric(38, 19) - Andere numerieke of decimale datatypen worden niet meegenomen.

  • varchar(8000),varchar(max),nvarchar(4000) en nvarchar(max) - Andere tekenstringdatatypes (zoals text, char(8000),nvarchar(30), enz.) worden niet overwogen.

  • varbinary(8000) en varbinary(max) - Andere binaire datatypes worden niet meegenomen (zoals image, binary(8000), varbinary(30), enz.).

  • Datum, tijd(7),kleindatumtijd,datumtijd, datumtijd2(7),datumtijdverschuiving(7) - Andere datum- en tijdtypes, zoals tijd(4), worden niet meegenomen.

  • sql_variant

  • xml

  • CLR-systeemgedefinieerde types (hiërarchie, geometrie, geografie)

  • Door de gebruiker gedefinieerde CLR-typen

Selectiecriteria

Van de kandidaat-datatypes wordt elk datatype dat de query ongeldig zou maken, afgewezen. Van de overige kandidaatdatatypes selecteert het type-deductie-algoritme er één volgens de volgende regels.

1. Het datatype dat het kleinste aantal impliciete conversies in E(@p) produceert, wordt gekozen. Als een bepaald datatype een datatype produceert voor E(@p) dat verschilt van TT(@p), beschouwt het type-deductiealgoritme dit als een extra impliciete conversie van het datatype van naar E(@p)TT(@p).

   Voorbeeld:

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

   In dit geval E(@p) is en Col_Int + @pTT(@p) is int. INT wordt gekozen voor @p omdat het geen impliciete conversies produceert. Elke andere keuze van datatype produceert ten minste één impliciete conversie.

2. Als meerdere datatypen gelijk zijn voor het kleinste aantal conversies, wordt het datatype met de hoogste prioriteit gebruikt. Voorbeeld:

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

   In dit geval produceren int en smallint één conversie. Elke andere datatype produceert meer dan één conversie. Omdat int voorrang heeft boven smallint, wordt int gebruikt voor @p. Voor meer informatie over de prioriteit van gegevenstypes, zie Voorrang van het datatype.

   Deze regel geldt alleen als er een impliciete conversie is tussen elk datatype dat volgens regel 1 aansluit en het datatype met de hoogste prioriteit. Als er geen impliciete conversie is, faalt de datatype-deductie met een fout. Bijvoorbeeld, in de query SELECT @p FROM tmislukt datatype-deductie omdat elk datatype voor @p even goed zou zijn. Er is bijvoorbeeld geen impliciete conversie van int naar xml.

3. Als twee vergelijkbare datatypes onder regel 1 vallen, bijvoorbeeld varchar(8000) en varchar(max), wordt het kleinere datatype (varchar(8000)) gekozen. Hetzelfde principe geldt voor nvarchar - en varbinaire datatypes.

4. Voor de doeleinden van regel 1 geeft het type-deductie-algoritme de voorkeur aan bepaalde conversies als beter dan andere. Conversies in volgorde van beste naar slechtste zijn:

   1. Conversie tussen hetzelfde basisdatatype van verschillende lengte.
   2. Conversie tussen vaste en variabele lengte van dezelfde datatypes (bijvoorbeeld char naar varchar).
   3. Conversie tussen NULL en int.
   4. Elke andere conversie.

Bijvoorbeeld, voor de query SELECT * FROM t WHERE [Col_varchar(30)] > @pwordt varchar(8000) gekozen omdat conversie (a) het beste is. Voor de query SELECT * FROM t WHERE [Col_char(30)] > @pwordt varchar(8000) nog steeds gekozen omdat het een type (b)-conversie veroorzaakt, en omdat een andere keuze (zoals varchar(4000)) een type (d)-conversie zou veroorzaken.

Als laatste voorbeeld, gegeven een query SELECT NULL + @p, wordt int gekozen voor @p omdat dit resulteert in een type (c)-conversie.

Permissions

Vereist toestemming om het @tsql-argument uit te voeren.

Voorbeelden

Het volgende voorbeeld geeft informatie zoals het verwachte datatype voor de niet-gedeclareerde @id en @name parameters.

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

Wanneer de @id parameter als @params referentie wordt gegeven, wordt de @id parameter weggelaten uit de resultaatset en wordt alleen de parameter @name beschreven.

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

Verwante inhoud

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