102 lines
3.3 KiB
Transact-SQL
102 lines
3.3 KiB
Transact-SQL
/* ===================================== */
|
|
/* Description: <Dinamikus sql az eszközök (T_ESZKOZ) lekérdezésére és/vagy exportjára> */
|
|
/* ===================================== */
|
|
DROP PROCEDURE IF EXISTS uspGetEszkoz
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[uspGetEszkoz]
|
|
@pTanevId int
|
|
,@pTeremId int = NULL
|
|
,@pFelelosId int = NULL
|
|
,@pKategoriaId int = NULL
|
|
,@pMinDarabszam int = NULL
|
|
,@pMaxDarabszam int = NULL
|
|
,@pNev nvarchar(255) = NULL
|
|
|
|
/* SimpleResult, Excel export */
|
|
,@SimpleResult bit = 0 -- ha true, akkor a visszaadott oszlopok szukebb listat adnak vissza, figyelve a join-ra és a where-re!
|
|
,@IsExport bit = 0
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @sql nvarchar(max) = ''
|
|
|
|
/* SELECT part */
|
|
SET @sql +=
|
|
+ N'
|
|
SELECT
|
|
e.ID AS ID
|
|
,e.C_NEV AS Nev
|
|
,e.C_TIPUS AS TipusId
|
|
,e.C_DARABSZAM AS Darabszam
|
|
' + IIF(@SimpleResult = 1, N'', N'
|
|
,e.C_BERELT AS IsBerelt
|
|
,e.C_BESZERZESDATUM AS BeszerzesDatum
|
|
,e.C_BESZERZESIAR AS BeszerzesiAr
|
|
,e.C_FOKONYVISZAM AS FokonyviSzam
|
|
,e.C_GYARISZAM AS GyariSzam
|
|
,e.C_GYARTASIEV AS GyartasiEv
|
|
,e.C_HALOZATIKAPCSOLAT AS IsHalozatiKapcsolat
|
|
,e.C_HORDOZHATO AS IsHordozhato
|
|
,e.C_INTERNETKAPCSOLAT AS IsInternetKapcsolat
|
|
,e.C_KATEGORIA AS KategoriaId
|
|
,e.C_LELTARISZAM AS LeltariSzam
|
|
,e.C_MEGJEGYZES AS Megjegyzes
|
|
,e.C_MULTIMEDIAS AS IsMultimedias
|
|
,e.C_NEMMUKODIK AS IsNemMukodik
|
|
,e.C_PEDAGOGUSHOZZAFERHET AS IsPedagogusHozzaferhet
|
|
,e.C_SZALLITO AS Szallito
|
|
,e.C_TANULOHOZZAFERHET AS IsTanuloHozzaferhet
|
|
,e.C_TARTOZEK AS IsTartozek
|
|
,e.C_TARTOZEKHIVATKOZAS AS TartozekHivatkozas
|
|
,t.ID AS TeremId
|
|
,t.C_NEV AS TeremNev
|
|
,f.ID AS FelelosId
|
|
,f.C_NYOMTATASINEV AS FelelosNev
|
|
,e.C_CELJA AS CeljaId
|
|
,e.C_JELLEGE AS JellegeId
|
|
,e.C_MENNYISEGIEGYSEG AS MennyisegiEgysegId
|
|
,e.C_VONALKOD AS Vonalkod
|
|
,f.C_SZULETESIDATUM AS FelelosSzuletesiIdo
|
|
,e.C_IMPORTALT AS Importalt') + N'
|
|
FROM T_ESZKOZ_OSSZES e
|
|
INNER JOIN T_TEREM_OSSZES t ON t.ID = e.C_TEREMID AND t.TOROLT = ''F'' AND t.C_TANEVID = e.C_TANEVID
|
|
' + IIF(@SimpleResult = 1, N'', N'
|
|
LEFT JOIN T_FELHASZNALO_OSSZES f ON f.ID = e.C_FELELOSID AND f.TOROLT = ''F'' AND f.C_TANEVID = e.C_TANEVID
|
|
')
|
|
|
|
/* WHERE part */
|
|
SET @sql += N'
|
|
WHERE
|
|
e.TOROLT = ''F'' AND e.C_TANEVID = @pTanevId
|
|
' + IIF(@pTeremId IS NULL, N'', N' AND e.C_TEREMID = @pTeremId') + N'
|
|
' + IIF(@pFelelosId IS NULL, N'', N' AND e.C_FELELOSID = @pFelelosId') + '
|
|
' + IIF(@pKategoriaId IS NULL, N'', N' AND e.C_KATEGORIA = @pKategoriaId') + '
|
|
' + IIF(@pMinDarabszam IS NULL, N'', N' AND @pMinDarabszam <= e.C_DARABSZAM') + '
|
|
' + IIF(@pMaxDarabszam IS NULL, N'', N' AND e.C_DARABSZAM <= @pMaxDarabszam') + '
|
|
' + IIF(@pNev IS NULL OR @pNev = '''', N'', N' AND e.C_NEV LIKE Concat(''%'',@pNev,''%'')') + ''
|
|
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pTanevId int
|
|
,@pTeremId int
|
|
,@pFelelosId int
|
|
,@pKategoriaId int
|
|
,@pMinDarabszam int
|
|
,@pMaxDarabszam int
|
|
,@pNev nvarchar(255)
|
|
,@SimpleResult bit
|
|
,@IsExport bit'
|
|
,@pTanevId = @pTanevId
|
|
,@pTeremId = @pTeremId
|
|
,@pFelelosId = @pFelelosId
|
|
,@pKategoriaId = @pKategoriaId
|
|
,@pMinDarabszam = @pMinDarabszam
|
|
,@pMaxDarabszam = @pMaxDarabszam
|
|
,@pNev = @pNev
|
|
,@SimpleResult = @SimpleResult
|
|
,@IsExport = @IsExport
|
|
|
|
END
|
|
GO
|