37 lines
1.1 KiB
Transact-SQL
37 lines
1.1 KiB
Transact-SQL
IF OBJECT_ID('[dbo].[fnGetKapcsolodoTantargyak]') IS NOT NULL BEGIN
|
|
DROP FUNCTION [dbo].[fnGetKapcsolodoTantargyak]
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION [dbo].[fnGetKapcsolodoTantargyak] (@pTantargyId INT)
|
|
RETURNS @return TABLE (
|
|
ID INT,
|
|
C_FOTARGYID INT,
|
|
C_NEV NVARCHAR(255),
|
|
C_TARGYKATEGORIA INT
|
|
)
|
|
BEGIN
|
|
IF @pTantargyId IS NULL
|
|
BEGIN
|
|
INSERT INTO @return
|
|
SELECT ID, C_FOTARGYID, C_NEV, C_TARGYKATEGORIA FROM T_TANTARGY WHERE TOROLT = 'F'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO @return
|
|
SELECT ID, C_FOTARGYID, C_NEV, C_TARGYKATEGORIA FROM T_TANTARGY WHERE ID = @pTantargyId AND TOROLT = 'F'
|
|
UNION
|
|
SELECT ID, C_FOTARGYID, C_NEV, C_TARGYKATEGORIA FROM T_TANTARGY WHERE C_FOTARGYID = @pTantargyId AND TOROLT = 'F'
|
|
UNION
|
|
SELECT ID, C_FOTARGYID, C_NEV, C_TARGYKATEGORIA FROM T_TANTARGY WHERE C_FOTARGYID = (SELECT C_FOTARGYID FROM T_TANTARGY WHERE ID = @pTantargyId AND TOROLT = 'F') AND TOROLT = 'F'
|
|
UNION
|
|
SELECT ID, C_FOTARGYID, C_NEV, C_TARGYKATEGORIA FROM T_TANTARGY WHERE ID = (SELECT C_FOTARGYID FROM T_TANTARGY WHERE ID = @pTantargyId AND TOROLT = 'F') AND TOROLT = 'F'
|
|
ORDER BY C_FOTARGYID ASC, C_NEV ASC
|
|
END
|
|
RETURN
|
|
END
|
|
|
|
|
|
GO
|
|
|