问创建计划指南

EN

declare @p1 int
set @p1=1986
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'SELECT Top 1 Amount,col2 from dbo.table1
                                                                                 where ExId=@P0
                                                                                 order by id
                                                 ',N'0a8e8e31-2a05-0000-8ece-0003fd69e692'
select @p1

DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (select cast(query_plan as nvarchar(max))  from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text (cp.plan_handle) st 
cross apply sys.dm_exec_query_plan (cp.plan_handle) qp 
where st.text like '%Top 1 Amount%'
and objtype='Prepared')

--select (@xml_showplan)



EXEC sp_create_plan_guide
@name = N'ForceuserLoanAmountRequests',
@stmt = N'SELECT Top 1 Amount,col2 from dbo.table1 where ExId=@P0 order by id', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P0 nvarchar(4000)',
@hints = @xml_showplan;

EXEC sp_create_plan_guide
@name = N'ForceuserLoanAmountRequests',
@stmt = N'SELECT Top 1 Amount,col2 from table1
                                                                                 where ExId=@P0
                                                                                 order by ID
                                                 ',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P0 nvarchar(4000)',
@hints = @xml_showplan;

GO