April 6, 2022 at 3:28 pm
SELECT sqltext.TEXT FROM sys.dm_exec_query_stats AS CP
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
I ran the said sql statement and get some sql scripts and the following is one of them
(@P1 nvarchar(10), @P2 nvarchar(20)) select * from purchline where purchid = @p1 and vendorcode= @p2
I tried to join with dm_exec_query_plan to get the values for @p1 and @p2 but I didn't the actual value of @p1 and @p2 from Query_plan cache, could you please tell me how to get the value of @p1 and @p2? many thanks!
April 6, 2022 at 6:06 pm
Have a look at https://www.sommarskog.se/query-plan-mysteries.html#dmvgettingplans where I have a query for this.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 7, 2022 at 10:27 am
Have a look at https://www.sommarskog.se/query-plan-mysteries.html#dmvgettingplans where I have a query for this.
thank you for your kind help! I'll study it
and I also find that it can't parameter value while using DBCC INPUTBUFFER(spid) sometimes, I'm not sure if we can use the same approach to get parameter value.
April 7, 2022 at 6:31 pm
If the batch is a stored procedure called through RPC, DBCC INPUTBUFFER will only give you database id and object id for the procedure. To my knowing there is no way to get the parameter values.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply