积极答复者
有哪个视图可以获取整个批处理代码

问题
-
batch代码为 WHILE 1=1 BEGIN exec sp_executesql N' SELECT * FROM [sss].[dbo].[test] WHERE [id]=@id EXEC [dbo].[aa] @test EXEC [dbo].[ab] @id', N'@test NVARCHAR(100) ,@id int',@test='你好',@id=2 END
sys.fn_get_sql(@plan_handle)
sys.dm_exec_sql_text(
上面两个视图,无论传入plan_handle还是sql_handle都无法获取整个批处理代码
------------------------------------------
--有时候获取到
(@test NVARCHAR(100) ,@id int)
SELECT * FROM [sss].[dbo].[test] WHERE [id]=@id
EXEC [dbo].[aa] @test
EXEC [dbo].[ab] @id
--------------------------------------
--有时候获取到
CREATE PROC aa (@test NVARCHAR(100))
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [NAME]=@testEND
-------------------------------------------------
--有时候获取到
CREATE PROC ab (@id int)
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [id]=@id
END----------------------------------------
其实我主要想知道出问题的sql是否调用了exec sp_executesql
Love SQL
全部回复
-
如果获取到(@test NVARCHAR(100) ,@id int) ,我就知道sql调用了exec sp_executesql
但是有时候
--------------------------------------
--有时候获取到
CREATE PROC aa (@test NVARCHAR(100))
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [NAME]=@testEND
-------------------------------------------------
--有时候获取到
CREATE PROC ab (@id int)
AS
BEGIN
SELECT * FROM [dbo].[test] WHERE [id]=@id
END----------------------------------------
Love SQL
-
exec sp_executesql 只有 profiler才能看到完整的
事件类型为RPC
找了一些资料,看来是没有办法了
SQL Batch statement和Remote Procedure Call 的区别
--http://stackoverflow.com/questions/12805692/sql-server-stored-procedure-no-statements-run-when-from-rpc
TSQL event and RPC event, difference example?
--https://social.technet.microsoft.com/Forums/sqlserver/en-US/6bd9e120-305a-492b-b14b-a3b93fec7a27/tsql-event-and-rpc-event-difference-example?forum=sqldatabaseengine
Love SQL