5

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?

5

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%';

enter image description here

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%';

enter image description here

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%'

enter image description here or

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.