0

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?

stonefree
  • 400
  • 1
  • 3
  • 8

2 Answers2

0

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

Jon B
  • 51,025
  • 31
  • 133
  • 161
Jacob Buter
  • 1
  • I don't think you need to declare variables in the SSRS query editor, it should automatically pull them out. – Sam Nov 28 '12 at 18:23
0

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

Sam
  • 7,543
  • 7
  • 48
  • 62