Invoking a scalar UDF as a Table UDF parameter in a View?
-
Those of you who helped with this one:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=107132
saw the development of a table UDF that returns all the people that work for a specific person based on that person's employee ID.
OK, next question:
I built a view to test using the above udf and this works:
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager('115898') udf_GetEMpsBYManager
Now, I don't want to be hardcoding employeeid's so I wrote a scalar udf, udf_GetEmployeeID that takes a full or partial name and returns a 6 character employeeid:
PRINT dbo.GetEmployeeID('Schaefer') returns '115898' so...
I tried to code that into the view, i.e.:
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager(dbo.GetEmployeeid('Schaefer'))udf_GetEMpsBYManager
But I get an error message:
Error in list of function arguments: '.' not recognized.
Incomplete parameters list.
Unable to parse query text.
Can I call a scalar udf as a parameter in the invocation of a table udf in a view?
There's a good one for you, huh?
-
declare @i int
select @i=dbo.GetEmployeeid('Schaefer')
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager(@i)udf_GetEMpsBYManager
-
I need to be able to do this in a view. Can you Declare variables in the SQL in a view? (I'd check myself, but I'm reading mail from home today and have no access to SQL client...). It's my impression you can't unless I missed something (which is quite possible).
-
No, you can't use parameters within a view. But you should have no problem using one or more functions in a view. Your syntax looks odd, though:
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager(dbo.GetEmployeeid('Schaefer'))udf_GetEMpsBYManager
Are you aliasing the function as "udf_GetEMpsBYManager" ? If so, why?
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager(dbo.GetEmployeeid('Schaefer'))
Signature is NULL
-
That's the syntax generated by Enterprise Manager when you select a Function from the Add Tables dialog. It's odd, but that's what it does.
I'm not really trying to use a parameter in a view. If you read the first post I have a view that works just fine invoking the udf_GetEmpsByManager function with a constant as the udf's parameter. I'm trying to find a way to substitute the "GetEmployeID" udf as the parameter for GetEmpsbyManager. This allows me to use the view by just changing the name in GetEmployeeID instead of having to know the person's Employee ID for GetEmpsByManager.
Yes, it would be much easier to code this in a Stored Procedure, but the ultimate use of the view is as a data source for an Excel spreadsheet. Excel's Query tool only accepts Tables and Views as sources from SQL, so I can't use an SP or UDF to build the table to pass to Excel.
No, I can't generate the Excel spreadsheet from the SP. The spreadsheet has to be pre-existing with the saved query so it can be imported into another software product. It's a long story, but there's a good reason why I'm trying to do it this way.
Maybe there's a way to incorporate the code in GetEmployeeID into GetEmpsByManager so that it expects a name, not an employee ID (thinking as I type here...). Hmm.... I'll have to look at that.
Still, if anyone can figure out how to make the code in the first post in the thread work in a view I would be a. astounded and b. greatly appreciative.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply