[es] - Dinamička stored procedura

goran85

Član broj: 212550
Poruke: 12
*.static.sbb.rs.

Profil

^{13.04.2011. u 14:14 - pre 158 meseci}

Interesuje me da li je moguće napraviti stored proceduru koja će imati dinamičke parametre.
Recimo hoću da napravim sledeće:

alter procedure nazivprocedure
@firmaid int null,
@param1 nvarchar(20) null,
@param2 char(2) null,
@param nvarchar(20) null
as
Begin
.

// ovde bih hteo da definišem sledeći dinamički uslov
//da @param1 uzima naziv kolone iz tabele koji mi želimo(recimo naziv, adresa ...)
//da @param2 uzima oznaku operatora koji mi želimo(=,<,>,between)
//da param3 uzima vrednost koju mi želimo
// trebalo bi ovako recimo da ispadne: @uslov= naziv="Posta"
.....

select firmaid, naziv, adresa ...
from firma
where firmaid=@firmaid and @uslov
....

Hvala unapred

Odgovor na temu

deerbeer
Beograd

Član broj: 174418
Poruke: 1189
*.dynamic.sbb.rs.

+395 Profil

Re: Dinamička stored procedura

^{13.04.2011. u 15:02 - pre 158 meseci}

Moze, tako sto ces da pozoves sp_executesql komandu i prosledis query koji ces da napravis od stringa
Mozda ovako nesto : http://www.marten-online.com/d...cute-dynamic-sql-in-mssql.html

Viva lollapalooza

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Dinamička stored procedura

^{13.04.2011. u 21:59 - pre 158 meseci}

Dinamicke stored procedure su opasan biznis. treba biti oprezan, jer mozes dobiti SQL injekciju. Verovatno najbolji tekst na web-u o dinamiskom SQL kodu se moze naci ovde:

http://www.sommarskog.se/index.html

I mnoge druge korisne stvari.

Odgovor na temu

goran85

Član broj: 212550
Poruke: 12
*.static.sbb.rs.

Profil

Re: Dinamička stored procedura

^{14.04.2011. u 07:51 - pre 158 meseci}

Čini mi se da nema rešenja oko toga da @param2 može da ima različite operatora kao vrednosti u upitu(<,>,=...)

Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*

+3 Profil

Re: Dinamička stored procedura

^{14.04.2011. u 09:23 - pre 158 meseci}

Code:

CREATE PROCEDURE [dbo].[DinamickaProcedura] @firmaid AS int ,
@param1 AS nvarchar(20) ,
@param2 AS char(2) ,
@param AS nvarchar(20)
AS
SET NOCOUNT ON
DECLARE @sql as nvarchar(1000)
DECLARE @ParmDefinition nvarchar(500)

DECLARE @Lfirmaid AS int
DECLARE @Lparam1 AS nvarchar(20)
DECLARE @Lparam2 AS char(2)
DECLARE @Lparam AS nvarchar(20)

SET @Lfirmaid=@firmaid
SET @Lparam1=@param1
SET @Lparam2=@param2
SET @Lparam=@param
DECLARE @uslov as nvarchar(200)
SET @uslov=@Lparam1+' '+@Lparam2
PRINT @uslov
SET @ParmDefinition=N'@firmaid AS int,@param1 AS nvarchar(20),@param2 AS char(2), @param AS nvarchar(20)'
SET @sql=N'select firmaid, naziv, adresa from firma where firmaid=@firmaid and '+ @uslov +'@param'
PRINT @sql
EXEC sp_executesql @sql,@ParmDefinition,@firmaid=@Lfirmaid,@param1=@Lparam1,@param2=@Lparam2,@param=@Lparam

Ovaj nacin je vrlo zgodan zbog izbegavanja CAST u dinamickom izrazu, odnosno pretvaranja u varchar, da bi se dobio @sql.

_{[Ovu poruku je menjao nadavesela dana 14.04.2011. u 10:38 GMT+1]}

_{[Ovu poruku je menjao nadavesela dana 14.04.2011. u 11:22 GMT+1]}

Odgovor na temu

goran85

Član broj: 212550
Poruke: 12
*.static.sbb.rs.

Profil

Re: Dinamička stored procedura

^{14.04.2011. u 10:46 - pre 158 meseci}

Hvala puno Nado, radi

Ono što bi mi sada trebalo je da uvedem i 4. parametar @param4, kojem ću dodeliti naziv stored procedure koju želim, i na osnovu nje da
mi SET @sql= ... dobija vrednost.(pr. SET @sql= exec mojaSP).

Takođe @param neće uvek biti tipa nvarchar, recimo nekad ću mu proslediti tip numeric ili datetime(pr datum>'2010-01-01').
Kako bi taj problem mogao da rešim?

Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*

+3 Profil

Re: Dinamička stored procedura

^{14.04.2011. u 10:57 - pre 158 meseci}

Citat:

Takođe @param neće uvek biti tipa nvarchar, recimo nekad ću mu proslediti tip numeric ili datetime(pr datum>'2010-01-01').
Kako bi taj problem mogao da rešim?

Ako kolona koju uporedjujes je tipa DATETIME, onda ces @param i@Lparam definirati kao datetime.
medjutim za svaki tip kolone moras imati i idgovarajuci tip parametra @paramnvarchar i @Lparamnvarchar.....

Dala sam resenje jer zelim da na primeru naucis da ga primenjujes. Za modifikacije, proanaliziraj isto, probavaj...
U samoj proceduri imas PRINT @uslov i PRINT@sql pa mozes da vidis kako izgleda UPIT i da vidis gde gresis.
Samo napred..treniraj.

Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*

+3 Profil

Re: Dinamička stored procedura

^{14.04.2011. u 11:21 - pre 158 meseci}

moja momentalna ideja, a tvoja dorada bi bila da definiras
DECLARE @Lparamnvarchar AS nvarchar(20)
DECLARE @Lparamdate AS nvarchar(20)
DECLARE @Lparam AS money
isto prosledi i tri tipa parametra @paramnvarchar,@paramdatetime,@parammoney
SET @Lparamnvarchar
ovaj deo je zasad tvoj :) i nesto ovako treba. Zavisno od tipa kolone ciji naziv saljes kao @param1
definises @Lparam ili kao @Lparamnvarchar ili @Lparamdate ili @Lparam.
Mozda i to treba biti deo @sql.

Odgovor na temu

rambo
Dejan Petković
Beograd

Član broj: 6095
Poruke: 190
*.static.isp.telekom.rs.

+6 Profil

Re: Dinamička stored procedura

^{15.04.2011. u 14:53 - pre 158 meseci}

Ako već praviš nešto ovako dinamičko, što onda ne kreneš na malo drugačiji način. Napraviš tabelu u kojoj su ti svi upiti koje želiš da jedna procedura izvršava, a proceduri samo šalješ parametre tipa koji upit želiš da se izvrši i sa kojim parametrima. Na kraju u SP koristiš sp_executesql i to je to. Ako nastaviš sa načinom koji si započeo, mislim da ćeš se u jednom trenutku zapetljati ili će ti ta procedura biti prilično kompleksa i komplikovana za održavanje.

Ako ovo ne radiš za vežbu, na osnovu ličnog iskustva mislim da ne trebaš da praviš ovako nešto, osim ako se radi o konačnom broju mogućnosti koje ta procedura treba da pokrije.

"There is a theory which states that if ever anybody discovers exactly what the
Universe is for and why it is here, it will instantly disappear and be replaced by
something even more bizarre and inexplicable. There is another theory which states
that this has already happened."
-- Douglas Adams

Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*

+3 Profil

Re: Dinamička stored procedura

^{15.04.2011. u 15:35 - pre 158 meseci}

Citat:

Dali mojaSP ima parametre?
Ako nema onda:
DECLARE @Lparametar4 as nvarchar(50)
SET @Lparametar4=@parametar4
SET @ParmDefinition=N'@parametar4 as nvarchar(50)'
SET @sql=N'EXECUTE @parametar4'
EXEC sp_executesql @sql,@ParmDefinition,@parametar4=@Lparametar4

ali predpostavljam da procedura ima parametre koji su razlicitog tipa za svaku od njih.

Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.

+79 Profil

Re: Dinamička stored procedura

^{15.04.2011. u 16:39 - pre 158 meseci}

Rambo govori istinu. Open concept procedure brzo postanu nemoguce za odrzavanja i nove promene pocinju da kvare sta smo pre imali. Civanje kompletnih skripti u bazi pa njihovo izvrsavanje uz prethodni REPLACE je veoma mocna stvar. provereno

Odgovor na temu

goran85

Član broj: 212550
Poruke: 12
*.dynamic.isp.telekom.rs.

Profil

Re: Dinamička stored procedura

^{15.04.2011. u 17:25 - pre 158 meseci}

Da li je moguce uraditi sledece:
select * from openquery(loopback,'ImeBaze.dbo.'+@parmaetar'''')

gde bi mi @parametar uzimao vrednost naziv stored procedure koja bi mi u tom trenutku trebala?
Kako god da uradim prijavljuje mi gresku/

Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*

+3 Profil

Re: Dinamička stored procedura

^{18.04.2011. u 09:24 - pre 158 meseci}

Za procedure sa parametrom

DECLARE @Lprocedura as nvarchar(50)
DECLARE @Lfirmaid AS int
SET @Lprocedura=@procedura
SET @Lfirmaid=@firmaid
SET @ParmDefinition=N'@procedura as nvarchar(50),@firmaid as int'
SET @sql=N'EXECUTE @procedura @firmaId'
PRINT @sql
EXEC sp_executesql @sql,@ParmDefinition,@procedura=@Lprocedura,@firmaId =@LfirmaId

Odgovor na temu

nadavesela
programer, DZS

Član broj: 199298
Poruke: 93
195.26.131.*

+3 Profil

Re: Dinamička stored procedura

^{18.04.2011. u 10:02 - pre 158 meseci}

Za procedure sa vishe parametara

DECLARE @Lprocedura as nvarchar(50)
DECLARE @Lfirmaid AS int
DECLARE @Lnaziv as nvarchar(30) --ovo parametar procedure koju pozivas
SET @Lprocedura=@procedura
SET @Lfirmaid=@firmaid
SET @Lnaziv=@param --ovo je vrednost parametra procedure koju pozivas koji dobija vrednost parametra u dinamickoj proceduri
SET @ParmDefinition=N'@procedura as nvarchar(50),@firmaid as int,@naziv as nvarchar(30)'
SET @sql=N'EXECUTE @procedura @firmaId , @naziv'
PRINT @sql
EXEC sp_executesql @sql,@ParmDefinition,@procedura=@Lprocedura,@firmaId =@LfirmaId,@naziv=@Lnaziv

Odgovor na temu