Dinamička stored procedura

Dinamička stored procedura 13.04.2011. u 15:14 · pre 189 meseci #1

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

Re: Dinamička stored procedura 13.04.2011. u 16:02 · pre 189 meseci #2

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

Potpis

Viva lollapalooza

Re: Dinamička stored procedura 13.04.2011. u 22:59 · pre 189 meseci #3

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.

Re: Dinamička stored procedura 14.04.2011. u 08:51 · pre 189 meseci #4

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

Re: Dinamička stored procedura 14.04.2011. u 10:23 · pre 189 meseci #5

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.

Re: Dinamička stored procedura 14.04.2011. u 11:46 · pre 189 meseci #6

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?

Re: Dinamička stored procedura 14.04.2011. u 11:57 · pre 189 meseci #7

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.

Re: Dinamička stored procedura 14.04.2011. u 12:21 · pre 189 meseci #8

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.

Re: Dinamička stored procedura 15.04.2011. u 15:53 · pre 189 meseci #9

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.

Potpis

"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

Re: Dinamička stored procedura 15.04.2011. u 16:35 · pre 189 meseci #10

Citat:

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).

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.

Re: Dinamička stored procedura 15.04.2011. u 17:39 · pre 189 meseci #11

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

Re: Dinamička stored procedura 15.04.2011. u 18:25 · pre 189 meseci #12

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/

Re: Dinamička stored procedura 18.04.2011. u 10:24 · pre 189 meseci #13

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

Re: Dinamička stored procedura 18.04.2011. u 11:02 · pre 189 meseci #14

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

Forum kao aplikacija na telefonu

Srodne teme