OUTPUT variables in Profiler

  • The following is what SQL Profiler shows for a stored procedure call:

    declare @p1 int

    set @p1=20611159

    exec dbo.Set_Phone_6 @p1 output,NULL,NULL,NULL,NULL

    select @p1

    go

    In the sproc, I have logic = if @p1 is null, then do an insert, set the value of @p1 to the newly created identity, and exit. If @p1 is not null, it's an update, do the update and exit.

    My developer is actually issuing the call to the sproc passing @p1 as NULL. The result of his call is an INSERT. Why is Profiler showing "set @p1=20611159"?

    Thanks!

  • What you're seeing there is what SQL server is passing back, or the "output". I'm assuming you're looking at the profiler event RPC:Completed and not RPC:Started? The later may give you what you're expecting. Keep in mind what an output parameter is for, it's for getting a result returned from the database. This same question has been asked before, here's a link:

    http://stackoverflow.com/questions/2198715/stored-procedure-output-parameters-in-sql-server-profiler

  • Right. And I didn't have RPC:Starting in my trace definition. Well that explains it.

    Thanks!

    P

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply