I'm using XEvents to analyze queries from a Spring application to a SqlServer DB (2019). Here's the statement
property of an rpc_completed
event. I don't understand what the set @p1=67
line does:
declare @p1 int
set @p1=67
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 int,@P2 bigint',N'update MyTable set Field1 = @P0, Field2 = @P1 where ID = @P2',N'0102A',123,999
select @p1
I first thought it set a value for @P1
(uppercase P), but that makes no sense because a different value for P1 (123) is passed to sp_prepexec. But otherwise, @p1
is only used as an output parameter, so why initialize it to anything? It doesn't seem to be a default value either as a similar set
occurs in multiple events with vastly different values. Does the set
have a purpose here?
Also, my server uses the SQL_Latin1_General_CP1_CI_AS collation. As this is case-insensitive (CI), I would have assumed that @p1
and @P1
reference the same variable in the exec
statement. As this doesn't seem to be the case, is the output part a separate scope from the query part in sp_prepexec?
CodePudding user response:
I am assuming that you are using an ODBC driver which uses an RPC call to sp_prepexec
to execute parameterized ad-hoc SQL batches. This is the equivalent of calling sp_prepare
and sp_execute
. These are internal stored procedures, used for executing SQL batches, and are not normally called directly from user code.
The select @p1
is just an artifact of the fact that the first parameter of sp_prepexec
is an OUTPUT
parameter. Even though it is unlikely to be used again, it could in theory be passed to sp_execute
to execute the batch a second time.
The code you are seeing is not actually what is executed. It's just a representation of the way OUTPUT
parameters are handled in an RPC call, so that if you wanted to execute this as a SQL batch, you could do so in SSMS. The actual call is a special RPC call direct to sp_prepexec
.
Note also that the @p0
and @P0
are different variables, not because of case insensitivity, but because they are in different scopes.
What you presumably are actually executing is
update MyTable set Field1 = @P0, Field2 = @P1 where ID = @P2
And you have the parameters @P0 nvarchar(4000) = N'0102A'
@P1 int=123
@P2 bigint=999
Note also that sp_prepexec
and friends can be problematic for performance, because they disable parameter sniffing. If possible, find an ODBC driver that uses sp_executesql
, which does not.