SQLTeam.com | Weblogs | Forums

Is it possible to select the name of a TVF from within it?

Is it possible to select the name of a TVF from within it?

I have built a series of reports & different users should see different data as a result of their level of access for a given report. By knowing their access key & the report they're trying to access, I can know what results to deliver.

The access key is straightforward enough. I can also manually write the name of the report as a parameter to the other TVF which filters the results. But is there a better way? Is it possible to collect the TVF name from a variable?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[tvf_Example](
	@access_key NVARCHAR(32)
	)
RETURNS TABLE
AS
RETURN

SELECT *
    -- Lots of data that I want to display
FROM table_with_data twd
INNER JOIN [dbo].[tvf_ValidatesUserAccess](	@access_key, 'tvf_Example') acc --- This part is currently hard-coded.  Can the second parameter be pulled from a variable?
	ON twd.shared_key = acc.shared_key
	
GO

Based on MS docs, I thought @@PROCID should work, but it doesn't work. @@PROCID works inside a procedure to get the name, but not for a function apparently.

Given that, I don't see how to do it. The function hasn't even been created yet, so the func name's not yet stored in a system table. So even if there were a way to retrieve it from a system table and use it in the function. that's not available.

@@PROCID does work for a multi-statement TVF but I can't get it to work for an inline TVF. Interesting.

What you're describing is basically row-level security:

Could you use that instead?