Récupérer dynamicment les noms de parameters et les valeurs actuelles à l'intérieur de la procédure stockée T-SQL

Vous n'avez pas besoin de récupérer dynamicment les noms et les parameters à l' intérieur du proc stocké, car une fois que le proc stocké est créé ou modifié, il ne peut pas changer ses parameters jusqu'à ce qu'il soit modifié ou recréé.

Au lieu de cela, vous pouvez avoir la list des parameters à l'intérieur de la proc statique stockée, mais pour ne pas enum manuellement les parameters, vous pouvez l'avoir générée dynamicment par le DDL sortinggger.

Définir des balises de commentaire, qui seront utilisées pour marquer l'endroit, où les parameters doivent être listés dans le proc stocké et les append au corps du proc stocké, le cas échéant. Le triggersur doit find des marqueurs et modifier proc en insérant une list statique des noms de parameters et leurs valeurs entre les marqueurs. L'exemple suit.

DDL-sortinggger

create sortinggger StoredProc_ListParams on database for CREATE_PROCEDURE, ALTER_PROCEDURE as begin set nocount on; if @@nestlevel > 1 return; declare @evt xml, @sch sysname, @obj sysname, @text nvarchar(max); set @evt = eventdata(); set @text = @evt.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]', 'nvarchar(max)'); if @text is NULL -- skip encrypted return; set @sch = @evt.value('(/EVENT_INSTANCE/SchemaName/text())[1]', 'sysname'); set @obj = @evt.value('(/EVENT_INSTANCE/ObjectNa1me/text())[1]', 'sysname'); declare @listParams nvarchar(max); set @listParams = ' select name, value from (values ' + stuff( (select ', ' + '(' + cast(p.parameter_id as varchar(10)) + ', ''' + p.name + '''' + ', cast(' + p.name + ' as sql_variant))' from sys.parameters p join sys.objects o on o.object_id = p.object_id and o.type = 'P' join sys.schemas s on s.schema_id = o.schema_id where s.name = @sch and o.name = @obj order by p.parameter_id for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 1, '') + ' ) p(num, name, value) order by num'; declare @startMarker nvarchar(100), @endMarker nvarchar(100); set @startMarker = '--%%LIST_PARAMS_START%%'; set @endMarker = '--%%LIST_PARAMS_END%%'; if left(@text, 6) = 'create' set @text = stuff(@text, 1, 6, 'alter'); declare @ixStart int, @ixEnd int; set @ixStart = nullif(charindex(@startMarker, @text), 0) + len(@startMarker); set @ixEnd = nullif(charindex(@endMarker, @text), 0); if @ixStart is NULL or @ixEnd is NULL return; set @text = stuff(@text, @ixStart, @ixEnd - @ixStart, @listParams + char(13) + char(10)); if @text is NULL return; exec(@text); end 

Le script du proc stocké pour le test:

 create procedure dbo.TestProc ( @id int, @name varchar(20), @someFlag bit, @someDate datetime ) as begin set nocount on; --%%LIST_PARAMS_START%% -- list params for me here, please --%%LIST_PARAMS_END%% end 

Et voici comment le proc stocké apparaît dans la database une fois le script de création ci-dessus exécuté:

 alter procedure [dbo].[Test] ( @id int, @name varchar(20), @someFlag bit, @someDate datetime ) as begin set nocount on; --%%LIST_PARAMS_START%% select name, value from (values (1, '@id', cast(@id as sql_variant)), (2, '@name', cast(@name as sql_variant)), (3, '@someFlag', cast(@someFlag as sql_variant)), (4, '@someDate', cast(@someDate as sql_variant)) ) p(num, name, value) order by num --%%LIST_PARAMS_END%% end 

La seule ressortingction avec cette approche est qu'elle ne fonctionnera pas avec les process stockés cryptés. Aussi, vous devrez faire quelques ajustements, si vous souhaitez gérer les parameters du type de table.

Je cherche un morceau standard de code que je peux coller dans la procédure qui peut boucler à travers tous les parameters pour le proc et récupérer les valeurs actuelles passées in–

Vous pouvez get toutes les valeurs passées pour un sp en utilisant la requête ci-dessous

Exemple :
J'ai ci-dessous proc stocké qui me donne les détails des ventes (pour la démo seulement)

 alter proc dbo.getsales ( @salesid int ) as begin select * from sales where cust_id=@salesid end 

J'ai appelé mon sp comme ci-dessous ..

 exec dbo.getsales 4 

Maintenant, si je veux get la valeur passée, je peux utiliser la requête ci-dessous

 select top 10* from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_text_query_plan(cp.plan_handle,default,default) where objtype='proc' 

ce qui m'a montré ci-dessous comme la valeur de time de compilation

cela dit, il y a beaucoup de choses à considérer … nous pouvons utiliser des methods xml pour get cette valeur

maintenant ce qui se passe, si je cours encore le même proc enregistré pour la valeur de 2 ..

 <ColumnReference Column="@salesid" ParameterComstackdValue="(4)" ParameterRuntimeValue="(2)" /> 

Une capture importante ici, est que les valeurs ci-dessus sont montrées quand j'ai choisi le plan d'exécution pour montrer de ssms.

Mais quelle sera la valeur dans le cache, permet de le voir en utilisant à nouveau la requête cache ci-dessus

 <ColumnReference Column="@salesid" ParameterComstackdValue="(4)"/> 

Il affiche toujours la valeur compilée, plus la colonne usecounts comme 5 – `ce qui signifie que ce plan a été utilisé 5 fois et le paramètre qui a été passé lors de la compilation du plan est 4.qui signifie également que les valeurs d'exécution ne sont pas stockées dans le cache plans détails ..

Donc, en résumé, vous pouvez get les valeurs d'exécution passées à proc stocké

• 1. Valeurs qui sont passées pendant que l'instruction est compilée (
  Vous pouvez commencer à rassembler ces informations sur une période de time et les save par rapport à la procédure stockée, je pense qu'avec le time avec les redémarrages du server, planifier les recompilations, vous pouvez get un nouvel set de valeurs de parameters)
• 2.En contact avec l'équipe DEV, c'est aussi un bon moyen, car ils peuvent vous donner la list complète des parameters qui peuvent être passés, si cet exercice est cubique