xS6UCeAa

1CREATE FUNCTION dbo.ParseGrad
2(
3	@Gradovi nvarchar(max),
4	@IDDrzava int
5)
6RETURNS @t TABLE
7(
8	Grad nvarchar(50),
9	IDDrzava int
10)
11AS
12BEGIN
13	DECLARE @s int
14	SET @s = 0
15	WHILE NOT @s = LEN(@Gradovi)
16	BEGIN
17		INSERT INTO @t VALUES(SUBSTRING(@Gradovi, @s + 1, CHARINDEX(';', @Gradovi, @s + 1) - (@s + 1)), @IDDrzava)
18		SET @s = CHARINDEX(';', @Gradovi, @s + 1)
19	END
20	RETURN
21END
22GO
23
24CREATE PROC dbo.UmetniDrzavaGrad
25(
26	@Drzava nvarchar(20),
27	@Grad nvarchar(50)
28)
29AS
30BEGIN
31	DECLARE @IDDrzava int
32	IF EXISTS (SELECT * FROM Drzava AS d WHERE d.Naziv = @Drzava)
33	BEGIN
34		SELECT @IDDrzava = d.IDDrzava FROM Drzava AS d
35		WHERE d.Naziv = @Drzava
36	END
37	ELSE
38	BEGIN
39		INSERT INTO Drzava VALUES (@Drzava)
40		SET @IDDrzava = SCOPE_IDENTITY()
41	END
42	
43	INSERT INTO Grad (Naziv, DrzavaID)
44	SELECT * FROM dbo.ParseGrad(@Grad, @IDDrzava) as g WHERE NOT EXISTS(SELECT * FROM Grad WHERE Grad.Naziv = g.Grad)
45END
46GO
47
48EXEC dbo.UmetniDrzavaGrad 'Zimbambwe', 'ZZagreb;ZSplit;ZRijeka;ZPozega;'