Dinamička stored procedura

[ goran85 @ 13.04.2011. 14:14 ] @

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

[ deerbeer @ 13.04.2011. 15:02 ] @

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

[ Zidar @ 13.04.2011. 21:59 ] @

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.

[ goran85 @ 14.04.2011. 07:51 ] @

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

[ nadavesela @ 14.04.2011. 09:23 ] @

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]}

[ goran85 @ 14.04.2011. 10:46 ] @

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?

[ nadavesela @ 14.04.2011. 10:57 ] @

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.

[ nadavesela @ 14.04.2011. 11:21 ] @

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.

[ rambo @ 15.04.2011. 14:53 ] @

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.

[ nadavesela @ 15.04.2011. 15:35 ] @

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.

[ Zidar @ 15.04.2011. 16:39 ] @

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

[ goran85 @ 15.04.2011. 17:25 ] @

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/

[ nadavesela @ 18.04.2011. 09:24 ] @

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

[ nadavesela @ 18.04.2011. 10:02 ] @

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