Unfamiliar syntax - Query with Parameters in Braces at the beggining

Question

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?

Answer 1

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

CREATE SCHEMA PERSON;

CREATE TABLE Person.Person( BusinessEntityID int );

INSERT INTO Person.Person(BusinessEntityID)
VALUES(1),(2),(3);

Query

exec sp_executesql N'SELECT  * FROM    Person.Person WHERE   BusinessEntityID = @be',N'@be int',@be=2

See the result in the cache with this query

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%Person%';

or

(@be int)SELECT  * FROM    Person.Person WHERE   BusinessEntityID = @be

With a procedure the caching will be different

Create the procedure

use test 
go
create procedure dbo.myproc @dbname varchar(255)
as
select * from sys.databases where name = @dbname 

Run the proc

exec dbo.myproc @dbname= 'master';

Result in cache

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%myproc%';

or

create procedure dbo.myproc @dbname varchar(255)  as  select * from sys.databases where name = @dbname 

This can also happen when forced parameterization is enabled

Enable forced parameterization

ALTER DATABASE test SET PARAMETERIZATION FORCED

Same query, without parameters

SELECT  *
FROM    Person.Person
WHERE   BusinessEntityID = 5

Result in cache

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%Person%'

or

(@0 int)select * from Person . Person where BusinessEntityID = @0