SQLTeam.com | Weblogs | Forums

Sp_prepexec, unable to see parameters


#1

Hi all,

I ran a profiler trace to capture what was taking the most load on my SQL Server.

I am seeing a lot of statements like:

declare @p1 int
set @p1=13833
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000)',
N'SELECT fullobject FROM openidm400.managedobjects obj
INNER JOIN openidm400.managedobjectproperties prop ON obj.id = prop.managedobjects_id
INNER JOIN openidm400.managedobjectproperties prop2 ON obj.id = prop2.managedobjects_id
INNER JOIN openidm400.objecttypes objtype ON objtype.id = obj.objecttypes_id
WHERE prop.propkey=''/''+@P0 AND LOWER(prop.propvalue) = LOWER(@P1) AND prop2.propkey=''/''+@P2 AND LOWER(prop2.propvalue) = LOWER(@P3)
AND objtype.objecttype = @P4',N'userName',N'hatestt17408@yopmail.com',N'type',N'customer',N'managed/user'
select @p1

Is there any way I can find out what parameters were used?, I can only see @P1 but not the others.


#2

Keep in mind the outer @p1 is different than then inner @p1. That's the number of the prepared statement.

sp_prepexec follows the pattern of parameter declaration, SQL statement, and parameter values. So @p0 is "userName", @p1 is the email address, @p2 is "type", et.c


#3

Thanks for the information!