XEvents: what is the purpose of this set statement for an output parameter?

Question

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?

Answer 1

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.