I created a custom User Profile property called "LocationID". I then created the following stored procedure to retrieve the LocationID based on UserID.

create procedure DNNUserIDToLocationID
@UserID int
as
select
b.PropertyValue as LocationID
from
dnn_ProfilePropertyDefinition a,
dnn_UserProfile b,
dnn_Users c
where
a.PropertyDefinitionID = b.PropertyDefinitionID
and b.UserID = c.UserID
and a.PropertyName = 'LocationID'
and c.UserID = @UserID

I am now trying to use this stored proc in a Report Grid query.

This works: (hard coded input)
DNNUserIDToLocationID 3

This does not: (dynamic @UserID input)
DNNUserIDToLocationID @UserID

error message:
Query variables detected!
Query returned 0 rows in 0 seconds.