Unfamiliar syntax - Query with Parameters in Braces at the beggining 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?
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