Uskladistena procedura koja izvrsava for petlju ...

[ nikitaGradov @ 20.04.2011. 10:33 ] @

Nisam siguran da naslov teme precizno opisuje sto hocu da pitam (da uradim), pa evo malog pojasnjenja: radim (upis u bazu) na scenariju 'one-to-many'. Recimo, 'kompanija' ima vise 'telefona' i kad se definise (unosi) nova 'kompanija', i njeni 'telefoni', zelio bih da iz jedne uskladistene procedure odradim upis u tabele 'Kompanija' i 'Telefoni'.
Znaci, imam jedan INSERT u tabelu 'Kompanija' i nekoliko (1 ili vise) INSERT-a u tabelu 'Telefoni'. Kazem, htio bih da to uradim iz jedne uskladistene procedure (jasno, da bude jedna transakcija), ali ono sto ja ne znam je: kako da u uskladistenoj proceduri odradim , da tako kazem, FOR petlju koja ce odraditi upis(e) u tabelu Telefoni? Da li je moguce napisati for petlju unutar uskladistene procedure (a da proceduri proslijedim, kao parametar, brojac FOR petlje)?

Da generalizujem: kako realizovati upis u bazu, u scenariju 'ONE-2-MANY', iz jedne uskladistene procedure?

ALTER PROCEDURE dbo.NovaKompanija
(
@par1 nvarchar(128),
@par2 bigint,
@par3 datetime,
@...,
@brojac int )
AS
BEGIN TRANSACTION
INSERT INTO dbo.Kompanije (par1, par2, par3, ...)
VALUES (@par1, @par2, @par3)
IF @@ERROR <>0
BEGIN
ROLLBACK
RAISERROR ('Greska prilikom upisa u tabelu Kompanija !', 16, 1)
RETURN
END

// DA LI JE MOGUCE OVAKO NESTO ?

FOR (i=0;i<@brojac;i++) // NE ZNAM DA LI POSTOJI OVAKVA SQL SINTAKSA???

INSERT INTO dbo.Telefon (IDKompanije, x, y)
SELECT IDENT_CURRENT ('dbo.Kompanije'), @x, @y
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Greska prilikom upisa u tabelu Telefoni !', 16, 1)
RETURN
END

// OVO BI BIO KRAJ FOR petlje

COMMIT
RETURN

Hvala na pomoci ...

[ Dusan Kondic @ 20.04.2011. 11:28 ] @

Petlje u SQL-u se izbegavaju jer su spore (n puta izvšena neka komanda ili komande).
Preporučujem da x i y podatke prvo INSERT uješ u neku privremenu tabelu, zatim izvršiš
upis u Kompanije, i na kraju upis u Telefon kao INSERT/SELECT. Nešto kao:

Code:

ALTER PROCEDURE dbo.NovaKompanija
(
@par1 nvarchar(128),
@par2 bigint,
@par3 datetime,
@...,
@brojac int )
AS
DECLARE @T TABLE (XPodatak INT, YPodatak INT)
DECLARE @Err INT

BEGIN TRANSACTION

INSERT INTO @T SELECT 'X source' AS XPodatak, 'Y source' AS YPodatak FROM NekiSource

SET @Err = @@ERROR
IF (@Err <> 0) GOTO PROBLEM

INSERT INTO dbo.Kompanije (par1, par2, par3, ...)
VALUES (@par1, @par2, @par3)

SET @Err = @@ERROR
IF (@Err <> 0) GOTO PROBLEM

INSERT INTO dbo.Telefon (IDKompanije, x, y)
SELECT IDENT_CURRENT ('dbo.Kompanije'), XPodatak, YPodatak FROM @T

SET @Err = @@ERROR
IF (@Err <> 0) GOTO PROBLEM

COMMIT TRANSACTION

PROBLEM:
IF (@Err <> 0) BEGIN ROLLBACK TRAN END

[ nikitaGradov @ 20.04.2011. 13:35 ] @

Hvala ti na odgovoru ...

Mozda sam, u postavci, trebao da navedem da koristim SQL Server 2005 (u stvari, za razvoj koristim SqlExpress 2005, a, najvjerovatnije, ce aplikacija raditi pod SQL Server-om 2008).
I drugo, a vjerovatno se podrazumijeva, podaci o 'telefonima' su u listi (radim u C#-u), znaci nesto ovako: List<Telefoni> telefoni.
Dakle, moje pitanje se prvenstveno odnosi na: da li uskladistenoj proceduri mogu da proslijedim parametar koji je tipa liste (niza, nebitno)? Naisao sam na podatak da je to moguce pod SqlServer0om 2008 - ali, kao sto rekoh, radim pod SqlServer-om 2005 ...

Razumio sam tvoju ideju (jos jednom hvala), ali samo da pitam:

> INSERT INTO @T SELECT 'X source' AS XPodatak, 'Y source' AS YPodatak FROM NekiSource

Da li gornji INSERT 'insertuje' samo jedan rekord, ili vise rekorda, u tabelu 'T', iz 'NekiSource' ?

>INSERT INTO dbo.Telefon (IDKompanije, x, y)
>SELECT IDENT_CURRENT ('dbo.Kompanije'), XPodatak, YPodatak FROM @T

Isto pitanje kao i gore: da li ovaj INSERT 'insertuje' SVE rekorde iz tabele 'T' u tabelu 'Telefon' ili samo jedan rekord?

Moje pitanje, prakticno, glasi: kako, u jednoj uskladistenoj proceduri, izvesti vise INSERT-a ?

Pokusavam da nesto 'izguglam' - ako nesto nadjem, 'prijavicu' .

Pozdrav

[ Zidar @ 20.04.2011. 14:24 ] @

MS SQL ni u jednoj verziji ne podrzava liste i nizove. To stvara problem kada dodjes u situaciju da treba u jednj transakciji da uneses vise redova u atbelu kroz proceduru. Resenje naravno postoji.

Moguce je proceduri poslati lsitu kao delimited string. Onda procedura taj delimited string pretvori u lokalnu tabelu (lokalna = vidljiva samo u okviru procedure) i t alokalna tabela se onda insertuje gde treba. Dobra praksa je imati generalnu funkciju koja pretvara liste u tabele - posaljes funkciji delimited string a funkcija vrati tabelu.

O tome kako se liste rpetvaraju u tabele imas ovde http://www.sommarskog.se/arrays-in-sql-2005.html

Primer? Evo primer. Sa pomenutog sajta skinucemo jednu od funkcija koje pretvaraju delimite string u tabelu. Clanak je veoma studiozan i daje gomilu resenja, svako bolje od sledecg, korak po korak. Tebi ostavljamo da prostudiras clanak i izaberes resenje koje ti najvise odgovara.

Code:

CREATE FUNCTION iter_charlist_to_tbl
                 (@list      nvarchar(MAX),
                  @delimiter nchar(1) = N',')
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          str     varchar(4000)      NOT NULL,
                          nstr    nvarchar(2000)     NOT NULL) AS

BEGIN
   DECLARE @endpos   int,
           @startpos int,
           @textpos  int,
           @chunklen smallint,
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000),
           @tmpval   nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SET @chunklen = 4000 - datalength(@leftover) / 2
      SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
      SET @textpos = @textpos + @chunklen

      SET @startpos = 0
      SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2, @tmpstr)

      WHILE @endpos > 0
      BEGIN
         SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
                                             @endpos - @startpos - 1)))
         INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
         SET @startpos = @endpos
         SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2,
                                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   INSERT @tbl(str, nstr)
      VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
END
GO

Funkciju direktno pozivas ovako:

Code:
SELECT
*
FROM dbo.iter_charlist_to_tbl
('905-453-9760,905-542-0481,1-800-639-2273,416-439-8668,1-866-600-6604',',')
;

Naravno da funkcija moze da se kombinuje sa drugim tabelama kroz JOIN ili na neki drugi nacin. Direktno sa sajta, primer procedure koja kombinuje funkciju sa tabelom iz Northwind baze:

Code:
CREATE PROCEDURE get_company_names_iter @customers nvarchar(2000) AS
   SELECT C.CustomerID, C.CompanyName
   FROM   Northwind..Customers C
   JOIN   iter_charlist_to_tbl(@customers, DEFAULT) s
     ON   C.CustomerID = s.nstr
go
EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'

Nadam se da je pomoglo

[ Dusan Kondic @ 20.04.2011. 14:42 ] @

Code:
INSERT INTO Destinacija(destinaciona polja) VALUES(neke vrednosti)

unosi samo jedan red

Code:
INSERT INTO Destinacija(destinaciona polja) SELECT izvorna polja FROM Izvor WHERE ...

unosi sve rodove koje SELECT odabere.

Ako tvoji podaci postoje negde u bazi, mogao bi da ih SELECT-uješ u privremenu tabelu, odnosno
u drugom slučaju da ih INSERT-uješ u tabelu Telefon.
Ako su ti podaci kreirani u front-end-u, i nalaze se u List<T>, u zavisnosti od tipa i broja redova, možda
mogu da se proslede proceduri kao parametar.
Ako ne, onda je rešenje da izvršiš prvi INSERT, a da za drugi kreiraš komandu kojom ćeš u petlji (front-end)
uneti sve redove jednim otvaranjem konekcije.

Code:

SqlCommand [] NizKomandi = new SqlCommand(List.Count);
--u for(int i = 0; i < List.Count; i++) petlji popuni NizKomandi komandama sa različitim parametrima
try
{
MyConnection.Open();
for(int i = 0; i < List.Count; i++)
{
NizKomandi[i].ExecuteNonQuery();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
MyConnection.Close();
}

... ili slično.
Cilj je da izbegneš otvaranje/zatvaranje konekcije jer je to spora operacija.
Ovo je, dakle, samo u slučaju da ne možeš da proslediš podatke iz List-a proceduri.
Kakvi su ti podaci u List-i i kog su tipa?

[ nikitaGradov @ 20.04.2011. 15:15 ] @

Za Zidara:
>Moguce je proceduri poslati lsitu kao delimited string. Onda procedura taj delimited string pretvori u lokalnu tabelu (lokalna = vidljiva samo u okviru procedure) i t alokalna tabela se onda insertuje gde treba. Dobra >praksa je imati generalnu funkciju koja pretvara liste u tabele - posaljes funkciji delimited string a funkcija vrati tabelu.

Pa izgleda da je ovo jedini nacin - na vise sajtova se pominje kao mogucnost (ili jedan od dva: postoji i mogucnost da se lista proslijedi kao xml tip podatka, ali nemam iskustva sa pretvaranjem liste u xml format).

Ako sam dobro shvatio, ja bih trebao da:
1. 'svoju' listu (iz aplikacije) pretvorim u delimited string,
2. a onda da taj delimited string posaljem, kao parametar tipa (recimo) nvarchar, uskladistenoj proceduri,
3. da, unutar uskladistene procedure, pozovem funkciju koja delimited string pretvori u tabelu,
4. i da tabelu insertujem u ciljnu tabelu.

U svakom slucaju, moracu malo da, kako si napisao, proucim primjere iz ovog linka ... Hvala ti na odgovoru i vremenu koje si mi posvetio ...

Za Dusana:
>Ako tvoji podaci postoje negde u bazi, mogao bi da ih SELECT-uješ u privremenu tabelu, odnosno
>u drugom slučaju da ih INSERT-uješ u tabelu Telefon.
>Ako su ti podaci kreirani u front-end-u, i nalaze se u List<T>, u zavisnosti od tipa i broja redova, možda
>mogu da se proslede proceduri kao parametar.

Podaci su u frontend-u.

>Kakvi su ti podaci u List-i i kog su tipa?
ID ... long (ovo je PK same tabele Telefoni)
IDCont ... long (ovo je FK i perdstavlja ID iz tabele Kompanije)
IDType ... int (zamislio sam da u istoj tabeli stoje telefoni i Kompanija i Kontakata (iz Kompanija), pa ovo tip kaze da li se IDCont odnosi na kompaniju ili kontakte iz kompanija)
Broj ... string (broj telefona ili faksa)
TipBroja ... int (zamislio sam da se u istoj tabeli cuvaju i brojevi telefona i faksova, pa ovaj tip kaze da li je u pitanju telefon ili faks)
VrstaTelefona ... int (kucni, posao, mobilni , ...)
Redova moze biti 1 ili vise ...

Postoji li mogucnost za prosledjivanje, ovakve liste, kao parametra uskladistenoj proceduri?

[ Zidar @ 20.04.2011. 16:32 ] @

Pazljivo i strpljivo procitaj clanak. U clanku se daje resenje kroz XML i kao CLR -(svodi se na pisanje koda u C# sto bi ti se dopalo)

Za resenje sa funkcijama, ako imas vise kolona da insertujes u tabelu, onda tvoja lista moze da izgleda ovako:
'Marko,Markovic,416-234-3350;Mladen,Delic,041-234-5678;Milka,Babovic,041-876-5432;Dragan,Nikitovic,;'
Imas dakle tri kolone. Delimiter u ovom slucaju z aredove je ';' a za kolone unutar reda je ','. Priomieti da za poslednji element niza, gde je ima Dragan Nikitovic, nismo dali telefon. To je da proverimo da li funkcija lepo radi i kad nedostaju vrednosti.

Funkcija ce ti razbiti niz na ovakvu tabelu:
Marko,Markovic,416-234-3350
Mladen,Delic,041-234-5678
Milka,Babovic,041-876-5432
Dragan,Nikitovic

Sada nekako razbijes redove na kolone. Posto je sve kontrolisano iz aplikacije, onda uvek znas tacno koliko ce biti elemenata-kolona u svakom redu. ne bi trebalo da bude problem da se string 'Marko,Markovic,416-234-3350' razbije na tri dela.

Srecan rad

[ Dusan Kondic @ 20.04.2011. 16:43 ] @

Dobro si sve razumeo.
Kako ti je Zidar objasnio ovo može da se odradi, ali s obzirom na strukturu tvoje tabele, ne bih ti to preporučio zbog opterećenja servera.
Preporučujem ti onaj primer sa nizom komandi.
Možeš da probaš da koristiš DataTable umesto List. Tako možeš da napunjenu DataTabelu komandom BulkCopy iskopiraš u SQL tabelu.

[ nikitaGradov @ 25.09.2012. 15:10 ] @

U medjuvermenu sam uspio da rijesim upis u parent i child tabelu iz jedne uskladistene procedure. Opisao bih postupak, mozda nekome bude od koristi ...

- SqlServer 2008 podrzava Table-Valued Parameters (TVP), odnosno, mogucnost prosledjivanja tabele, kao parametra za uskladistene procedure,
- kao prvo, treba kreirati UDDT (User Defined Data Types), na nivou baze podataka, koji su tipa tabele (TABLE) i cije su strukture identicne strukturi tabela koje INSERT-ujemo.
Recimo da u bazi postoji tabele pod nazivima: ParentProba i ChildProba.
Potrebno je, kao prvi korak, kreirati nove tipove podataka, koji bi se, recimo, zvali: ParentProbaUdt i ChildProbaUdt:
CREATE TYPE ParentProbaUdt AS TABLE
(
-- upisati polja, identicna kao u tabelama ParentProba i ChildProba
)

- u koraku 2, kreiramo uskladistenu proceduru, ciji ce parametri biti tipa: ParentprobaUdt i ChildProbaUdt, recimo ovako:
CREATE PROCEDURE InsertParentChild
(
@ParentPar AS ParentProbaUdt READ ONLY,
@ChildPar AS ChildProbaUdt READ ONLY
)
AS ...
...
END

- u C# kodu treba pripremiti (ili da kazem 'spakovati') podatke u promenljive tipa DataTable, cije kolone odgovaraju kolonama tabela koje upisujemo (ParentProbaUdt i ChildProbaUdt)

- uskladistenu proceduru, iz C# aplikacije, pozivamo standardno, koriscenjem promenljive tipa SqlCommand, kojoj dodajemo parametre koji su tipa DataTable (odnosno, ParentProbaUdt i ChildProbaUdt). Za tip parametra treba izabrati 'Structured'.

- na kraju, samo jedan poziv metoda 'ExecuteNonQuery', poziva uskladistenu proceduru, koja ce izvrsiti INSERT parent i child podataka.

Kao sto rekoh na pocetku, mozda ce nekome biti od koristi ... ja sam ovakve scenarije uvijek rjesavao iz koda. A kada sam ja pocinjao da ucim C#, neko mi je rekao da 'u mom kodu ne bi trebalo da se nadje nijedna sql transact komanda, odnosno, da sve treba rjesavati kroz uskladistene procedure' ... evo, zahvaljujuci opciji TVP (Table-Valued Parameters), moguce je napisati, relativno jednostavno, i proceduru za istovremeni upis po shemi parent/child, odnosno, '1 prema vise'.
Evo i korisnog linka: http://lennilobel.wordpress.co...rators-a-match-made-in-heaven/

[ Zidar @ 27.09.2012. 20:17 ] @

@ nikitaGradov: Bravo