Syntax:
CREATE FUNCTION [owner_name.] function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n])
RETURNS TABLE
[WITH {ENCRYPTION|SCHEMABINDING}]
[AS]
RETURN [()SELECT-statement[]]
1.First,create following table-valued function:
CREATE FUNCTION Role_Camera (@role_name NVARCHAR(100))
RETURNS TABLE
AS
RETURN (SELECT DISTINCT C.* From Camera AS C, RolePurviewMapping AS RPM WHERE C.CameraName=RPM.EquipmentID
AND C.CameraName IN (SELECT EquipmentID FROM RolePurviewMapping WHERE RoleName=@role_name))
2.Call the above table-valued function:
SELECT * From Role_Camera(N'Administrators')
3.Finally,get following result: