2

I was trying to fine 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?

Many thanks

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
1
  • I was just wondering if anyone could explain my above question as I still have been unable to explain it?
    – Tom
    Apr 22, 2014 at 11:12

1 Answer 1

1

This is problematic the program is declaring a query whose results will be spooled on the server. The the program will call for one record at a time from the result set. The literal variables (p1,p2,p5,p6,p7) should be related to the configuration of the system stored procedure. The stringed variables (p1-p7) are represented by the last 7 terms of the procedure call.

    exec sp_cursorprepexec 
        @p1 output, -- plan handle for statement after its prepared
        @p2 output, -- cursor, the the program can retrieve one row at a time from the result set
        N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',--parameters for execution
        N'<<query text params here reference the list above>>', --statement, parameters here are the ones form the string list above
        @p5 output, --
        @p6 output, -- configuration terms see the docs
        @p7 output, -- 
        -- sql server will compile the query with unknown values for @P1 - @P7
        -- these values will be substituted for @P1-@P7 after its compiled
        1, --@P1
        4,
        1,
        3,
        1,
        8,
        1 --@P7

docs for this http://msdn.microsoft.com/en-us/library/Ff848775%28v=sql.105%29.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.