Sp sql 2008 con parametro con più valori

mercoledì 28 ottobre 2009 - 16.25

ilario Profilo | Senior Member

ciao

come posso passare più di n valore allo stesso parametro,

devo fare una select del tipo

select ...... from tabella where nomeparametro = 1 or 2 or a 3 e così via

a priori non so quanti valori possa avere

grazie

ciao

ilario

Pinky Profilo | Junior Member

Non esiste una funzionalità per fare questo in T-SQL e non è proprio banale.
Se i possibili valori sono di un numero massimo ragionevole (0..5,6,7) ti consiglio di usare N parametri che possono essere nulli.
Nelle SP è facile gestire (= eludere il controllo su) parametri nulli.

Altrimenti ecco dei link per passare un array di parametri ad una SP (operazione che non è presentein T-SQL).
1) In pratica costruisce e fa eseguire la query nella SP, perdendo tutti i vantaggi della SP soprattutto per performance ma semplice da implementare:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2) Questo (http://blogs.ugidotnet.org/netTools/archive/2007/02/13/70675.aspx) dice che è molto performante usare XML e rimanda a
http://sqlblog.com/blogs/peter_debetta/archive/2007/02/12/multiple-valued-parameters.aspx
e http://msdn.microsoft.com/it-it/library/cc185040.aspx (il paragrafo "Passare un array di dati ad una stored procedure")

Non so se esistono altri modi.

Ho visto ora che non parli di store procedure (me lo sono sognato io).
Allora la sintassi per fare quello che vuoi è:
select field_1, field_2, field_3 from myTable where field_1 IN (a,b,c,d)
Devi usare IN e separare i valori con la virgola.

Ciao
Alessandro

lbenaglia Profilo | Guru

>come posso passare più di n valore allo stesso parametro,

Ciao Ilario,

SQL Server 2008 risolve definitivamente questo problema introducendo i Table-Valued Parameters:
http://technet.microsoft.com/en-us/library/bb510489.aspx
http://msdn.microsoft.com/en-us/library/bb675163.aspx

>grazie
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

alx_81 Profilo | Guru

>ciao
ciao

>come posso passare più di n valore allo stesso parametro,
>devo fare una select del tipo
>a priori non so quanti valori possa avere
prima di tutto, che RDBMS utilizzi?

-- EDIT --
Non avevo visto l'oggetto del post. Chiedo scusa.

--

Alessandro Alpi | SQL Server MVP

http://www.alessandroalpi.net
http://blogs.dotnethell.it/suxstellino
http://mvp.support.microsoft.com/profile/Alessandro.Alpi
http://italy.mvps.org

Pinky Profilo | Junior Member

Questa si che è una bella novità! Sapevo solo del Hierarchy (fantastico) ed i nuovi tipi di campo.

ciao

lbenaglia Profilo | Guru

>Questa si che è una bella novità! Sapevo solo del Hierarchy (fantastico)
>ed i nuovi tipi di campo.
Le novità sono un "pelino" più numerose:
http://msdn.microsoft.com/en-us/library/bb500435.aspx

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

ilario Profilo | Senior Member

grazie mille

vado subito a studiarle

ciao
ilario

ilario Profilo | Senior Member

ciao
ho provato a studiare i link ma mi devo essere perso da qualche parte

non riesco a capire come devo impostare il parametro nella store procedure

ALTER PROCEDURE [dbo].[sp_ArticoliVendita_SelCat]
-- Add the parameters for the stored procedure here
@idcategoriemercsub ********** tabletype Singolarmente è un int
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select idarticolivendita ,codice,descrizione from articolivendita where idcategoriemercsub in (@idcategoriemercsub) order by descrizione Asc

END


@idcategoriemercsub ********** tabletype Singolarmente è un int
vorrei passare una datatable


come posso fare

grazie
ilario

Pinky Profilo | Junior Member

Se ho capito bene devi prima crearti il tuo tipo (immagine in allegato), come spiegato qui: http://msdn.microsoft.com/en-us/library/bb522526.aspx.
A te serve che contenga un solo campo di tipo int.

Se ho capito bene puoi passare alla store procedure il parametro che conterrà il tuo table-type SOLO partendo da un oggetto di tipo DataReader, DataTable o un IEnumerable (come dice qui: http://msdn.microsoft.com/en-us/library/bb675163.aspx).

Ad esempio per passare come parametro un array di id ruoli ho fatto:
CREATE TYPE RoleIdsParameter AS TABLE
(
roleId int
)

Poi l'ho testata così (query in Sql Server Management Studio):
declare @ids as RoleIdsParameter; insert into @ids (roleId) values (1), (2), (3); -- <<< equivale ad inserire 3 rows (usa table-valued del Sql 2008) -- se i campi fossero di più sarebbe: -- values (1, 'record 1', getDate()), (2, 'record 2', getDate()), (3, 'record 3', getDate()) select * from Roles where id in (select roleId from @ids); -- so che IN è da evitare, provo così (non so se corretto): select Roles.* from Roles inner join @ids as RoleFilter on Roles.id = RoleFilter.roleId;


E adesso provo a richiamarla da codice, come dice qui: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Non posso provarla perchè non ho il SqlCommand a portata di mano, uso un oggetto di libreria che accetta la stringa (query o nome della store procedure), la estringa di connessione e la lista di parametri, e fa il "lavoro sporco" dietro le quinte senza farmi toccare la connessione e gestendo automaticamente la creazione del SqlCommand.
Questo modo di procedere non mi ha mai dato problemi tranne che ora, spero di trovare (quando ne avrò bisogno) il modo di usarla senza modificare la libreria.


Ciao

Pinky Profilo | Junior Member

Scusa, forse sono arrivato al tuo problema solo ora.
Non ti accetta il tuo user-defined table type come tipo del parametro della store procedure, giusto?

Il parametro deve essere definito read-only:
@data RoleIdsParameter READONLY

Poi se hai problemi di permessi sull' "ECXECUTE", io ho dato (ho fatto dei test a caso...) i permessi "Control", "View Definition" e "References" all'utente di database.
In allegato l'immagine che mostra questo.


Per il "mio" problema, (non avere il Connection a cui addizionare il parametro), ho risolto così:

List<int> selectedRoles = new List<int>() {1,2,3}; // lista ruoli da filtrare IEnumerable list_2 = (IEnumerable) selectedRoles; // cast is OK DataTable dataTable = new DataTable(); dataTable.Columns.Add("Item", typeof(int)); foreach (var roleId in selectedRoles) { DataRow row = dataTable.NewRow(); row.SetField<int>( "Item", roleId); dataTable.Rows.Add( row ); } SqlParameter pRoles = new SqlParameter("@data", SqlDbType.Structured ); pRoles.SqlDbType = SqlDbType.Structured; pRoles.TypeName = "dbo.RoleIdsParameter"; //pRoles.Value = (IEnumerable)selectedRoles; // NON funziona pRoles.Value = dataTable; // Funziona

Solo che non capisco perchè NON riesca ad usare il List<T> direttamente (anche pre-castato a IEnumerable), per ora non mi serve ... lascio li.


Ciao

Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5