April 22, 2014 at 5:15 am
Hi
I was trying to find the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed. Is anyone able to explain was SQL server is doing here?
declare @p1 int
set @p1=1073764508
declare @p2 int
set @p2=180527133
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',N'SELECT A.POPUP,A.EMAIL,A.CREATEDBY,A.BATCHJOBID,101090,B.EMAIL,B.EVENTPOPUPDISPLAYWHEN,B.EVENTEMAILALERTSWHEN,B.ID,101090,C.LANGUAGE,C.ID,101090,D.RECID,D.STATUS,D.CAPTION,D.COMPANY,D.BATCHJOBID,D.STARTDATETIME,D.STARTDATETIMETZID,D.ENDDATETIME,D.ENDDATETIMETZID,D.ORIGSTARTDATETIME,D.ORIGSTARTDATETIMETZID,D.CANCELEDBY FROM BATCHJOBALERTS A,SYSUSERINFO B,USERINFO C,BATCHJOBHISTORY D WHERE (A.CREATEDBY=B.ID) AND (C.ID=B.ID) AND (((D.ALERTSPROCESSED=@P1) AND (D.BATCHJOBID=A.BATCHJOBID)) AND ((((D.STATUS=@P2) AND (A.BATCHJOBENDED=@P3)) OR ((D.STATUS=@P4) AND (A.BATCHJOBERROR=@P5))) OR ((D.STATUS=@P6) AND (A.BATCHJOBCANCELED=@P7))))',@p5 output,@p6 output,@p7 output,1,4,1,3,1,8,1
select @p1, @p2, @p5, @p6, @p7
Many thanks
April 22, 2014 at 6:39 am
Only the output parameters are listed, but if you look at the values at the end, there are 7, same as the number of parameters you have defined within the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2014 at 7:28 am
April 22, 2014 at 8:19 am
Thanks both
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply