1

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?

l4mpi
  • 5,103
  • 3
  • 34
  • 54
  • An `OUTPUT` parameter in T-SQL is both input and output, so the procedure can use the incoming value as well as set it when outgoing. There is as far as I can tell no point to this for `sp_prepexec` as opposed to `sp_execute` (the incoming value is ignored and not reused) but it's still legal. And yes, the `@p1` declared in the outer scope has nothing to do with the `@P1` of the statement: this is why the parameter list of the statement to be prepared is passed as a string. – Jeroen Mostert Aug 31 '22 at 13:27
  • 1
    In fact, the `@p1` you see here likely doesn't actually exist as a named value, but is just how the `sp_prepexec` TDS packet is translated (although only a packet trace could tell that for sure). Similar pseudo-statements are generated for reset and login, with the difference that `sp_reset_connection` doesn't even exist as an actual sproc, while `sp_prepexec` does. – Jeroen Mostert Aug 31 '22 at 13:30
  • @JeroenMostert so if I understand what you're saying correctly, setting the value of an output parameter _can_ be useful if the procedure makes use of it, but is nonsense here? – l4mpi Aug 31 '22 at 13:31
  • It appears to be unnecessary, yes. I don't actually know if `sp_prepexec` uses the input at all; a quick test shows that passing an invalid handle will give an error, but passing a handle of a statement already prepared earlier *still* generates a new handle rather than echoing the input. So it's not that `sp_prepexec` ignores the input value entirely. – Jeroen Mostert Aug 31 '22 at 13:34
  • @Charlieface: No, your second sentence is not true. Like I said, `OUTPUT` merely means the parameter is also available for output, *not* that it's ignored on input. `DECLARE @i INT = 42; EXEC sp_prepexec @i OUTPUT, NULL, N'SELECT 1'` will fail as the input value is still validated. Unfortunately I have little experience with explicitly prepared statements, so I don't know the exact semantics of `sp_prepexec`, and the docs are none too clear on the matter. – Jeroen Mostert Aug 31 '22 at 13:43

1 Answers1

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Thanks for the extensive information. The driver is an older version (9.x) of mssql-jdbc, and it does use `sp_executesql` for other queries, along with `sp_execute`. That might be an issue with the old version, or maybe there are valid reasons to use `sp_prepexec` for some specific queries... – l4mpi Aug 31 '22 at 14:09