USE AdventureWorksOBP

CREATE TYPE udOsoba AS TABLE (
	Ime NVARCHAR(50),
	Prezime NVARCHAR(50)
)

DECLARE @Ljudi udOsoba
INSERT INTO @Ljudi VALUES ('Ana', 'Anić')
INSERT INTO @Ljudi VALUES ('Miro', 'Mirić')

SELECT * FROM @Ljudi

/* kada koristimo kastm dejta tajp moramo staviti READONLY opciju kada 
	postavljamo npr unutar procedura
	primjer
	CREATER OR ALTER pProc
		@Ljudi udOsoba READONLY
*/

DROP TYPE udOsoba

DECLARE @json NVARCHAR(max)

SET @json = '
	{
		"OSOBE":
		[
		{"OSOBA":
			{"idosoba": 1,"ime": "Pero", "prezime": "Perić"}},
		{"OSOBA":
			{"idosoba": 2, "ime": "Iva", "prezime": "Ivić", "ostalo":
				{"email": "iva.ivic@algebra.hr", "telefon": "091 222 3333"}}}
		]
	}
'

SELECT * FROM OPENJSON(@json, '$.OSOBE')
WITH
(
	Ime NVARCHAR(50)	'$.OSOBA.ime',
	Prezime NVARCHAR(50)	'$.OSOBA.prezime',
	Email NVARCHAR(50)	'$.OSOBA.ostalo.email',
	Telefon NVARCHAR(50)	'$.OSOBA.ostalo.telefon'
)

SELECT k.IDKupac as [OSOBA.idkupac],
	k.Ime as [OSOBA.ime],
	k.prezime as [OSOBA.prezime],
	email as [OSOBA.ostalo.email],
	telefon as [OSOBA.ostalo.telefon]
	FROM Kupac as k
FOR JSON PATH, ROOT('OSOBE')

CREATE TYPE udMyKupac AS TABLE
(
	IDKupac INT
)
GO

CREATE OR ALTER PROC spZad1
	@Kupci udMyKupac READONLY
AS
	SELECT * FROM Kupac WHERE IDKupac IN (SELECT IDKupac FROM @Kupci)

DECLARE @t udMyKupac
INSERT INTO @t VALUES (1),(2),(3)
SELECT * FROM @t
EXEC spZad1 @t


CREATE OR ALTER PROC spProcZad1
	@json NVARCHAR(max)
AS
	SELECT * FROM OPENJSON(@json, '$.OSOBE')
	WITH (
		Ime NVARCHAR(50)		'$.OSOBA.ime',
		Prezime NVARCHAR(50)	'$.OSOBA.prezime'
	)

DECLARE @json NVARCHAR(max)
SET @json = '
	{"OSOBE":
		[
			{"OSOBA":
				{"ime": "Pero", "prezime": "Perić"}},
			{"OSOBA":
				{"ime": "Iva", "prezime": "Ivić"}}
		]
	}
'

EXEC spProcZad1 @json