I have run sp_WhoIsActive on one of our servers using the following syntax:
sp_whoisactive @get_plans = 1, @show_sleeping_spids = 0, @get_outer_command = 1, @get_locks = 1
and found a spid with the sql_command (the column shown when @get_outer_command
is set to 1) as follows
(@p1 int,@p2 int)
Exec MyDatabase.MyProc @p1 @p2
When I try to run a query with this syntax on my test Adventureworks database:
(@be int)
SELECT *
FROM Person.Person
WHERE BusinessEntityID = @be
I get the error
Msg 1050, Level 15, State 1, Line 1
This syntax is only allowed for parameterized queries.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@FN".
so it seems to be something to do with parameterized queries. This makes sense as the variable @be is never set to a value
What is happening here?
Best Answer
You are right, the (@be int) shown applies to parameterized queries. Applications often parameterize the queries using
sp_executesql
, and then sent them to the sql server.The query will be cached as (variables)QueryText . And ofcourse, the values won't be cached in the text, since the query is parameterized.
Parameterized query example
Test Data
Query
See the result in the cache with this query
or
With a procedure the caching will be different
Create the procedure
Run the proc
Result in cache
or
This can also happen when forced parameterization is enabled
Enable forced parameterization
Same query, without parameters
Result in cache
or