SSRS Data Source View Parameter in Function

Question

IN SSRS (SQL Server 2008 Standard), I have a table valued function with one input parameter. In attempting to create a new Named Query in the data source view, I have entered:

    SELECT        Family, Steet, userName
    FROM            dbo.F_Family(@userName) AS F_Family

I can run the SQL and get prompted for the input variable. However, when attempt to save the Named Query, I get the error: Must declare scalar variable @userName

Is what I am trying even possible? And if so, where do I declare the variable?

Answer 1

Try wrapping your code in a stored procedure and then calling that from SSRS.

Answer 2

Start with:

DECLARE @username varchar(200)
SET @username = 'username'

SELECT Family, Steet, userName
    FROM            dbo.F_Family(@userName) AS F_Family

That should do the trick