Stored Procedure + nomi di campi dinamici

giovedì 11 marzo 2010 - 11.24

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 11:24

Ciao amici.

Sto utilizzando delle SP su sql server 2008

Esempio:
SP che esegue una ricerca sul campo nome per il valore passato al parametro. (@nome=giovanni)

Mi chiedevo: e se devo fare la stessa identica ricerca sul campo cognome, cosa devo fare, da ciò che ho appreso fin'ora, una nuova SP che agisce sul campo cognome e parametro (@cognome=rossi)

LA domanda è la seguente:
Non è possibile passare alla SP oltre che il valore del parametro, anche il nome del campo su cui eseguire la ricerca?
Così da scrivere una sola SP ed usarla per diversi tipo di ricerca, Chiaramente sempre sulla stessa tabella!

Grazie

Riccardo D'Aria

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 mar 2010 - 12:04

>Non è possibile passare alla SP oltre che il valore del parametro,
>anche il nome del campo su cui eseguire la ricerca?
Senza ricorrere al Dynamic SQL? No.
E' il caso di ricorrere al Dynamic SQL? No se non vuoi subire attacchi di SQL Injection.

>Grazie
Prego.

Ciao!

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

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 16:04

>Senza ricorrere al Dynamic SQL? No.

Cosa intendi per il Dynamic SQl?

Quello tipo questo?
StrSQL= "Select * from Articoli where " & nomeCampo & " like '" & testo & "%'"

Grazie

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

Riccardo D'Aria

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 mar 2010 - 16:10

>Cosa intendi per il Dynamic SQl?
>
>Quello tipo questo?
>StrSQL= "Select * from Articoli where " & nomeCampo & " like
>'" & testo & "%'"
Esatto, anche se quel codice mi sa tanto di Visual Basic

>Grazie
Prego.

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

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 16:38

Certo che è visual basic.

Ma voglio usare le stored procedure e lo sto facendo.

Qui ho trovato questo esempio, che ho adattato con i miei dati, per rendere dinamica la cosa:

CREATE PROC [dbo].[set_position] (

@codice nvarchar(30),
@nomeCampo nvarchar(30)

)

AS
declare @strsql varchar(255)

BEGIN
set @strsql = 'SELECT * FROM articoli WHERE ' + @nomeCampo + ' Like ' + @codice +'%'
exec( @strsql)

END

ma mi da un errore sulla terminazione della stringa, mi dice che la sintassi è errata vicino a: "%".
non gli piace così impostato?

Grazie

Riccardo D'Aria

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 17:09

Dunque per concludere:

Utilizzando le SP se hai bisogno di fare 30 interoorgazioni sulla stessa tabella una, ad esempio, per ogni campo della tabella, bisogna creare 30 SP differenti giusto?

E' questa la strada da percorrere e la migliore?

Grazie
Riccardo D'Aria

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 mar 2010 - 17:13

>Certo che è visual basic.
>
>Ma voglio usare le stored procedure e lo sto facendo.
Sai a che rischi stai andando incontro?
http://www.dotnethell.it/articles/SQL-Injection-Tutorial-Security.aspx

>Qui ho trovato questo esempio, che ho adattato con i miei dati,
>per rendere dinamica la cosa:
>
>CREATE PROC [dbo].[set_position] (
>
>
>@codice nvarchar(30),
>@nomeCampo nvarchar(30)
>
>)
>
>AS
>declare @strsql varchar(255)
>
>BEGIN
>set @strsql = 'SELECT * FROM articoli WHERE ' + @nomeCampo +
>' Like ' + @codice +'%'
>exec( @strsql)
>
>END
>
>
>ma mi da un errore sulla terminazione della stringa, mi dice
>che la sintassi è errata vicino a: "%".
>non gli piace così impostato?
Posta un esempio completo con la struttura della tabella Articoli (CREATE TABLE), alcune righe di prova (INSERT INTO) un esempio di chiamata della tua sp ed il result set atteso.

>Grazie
Prego.

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

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 mar 2010 - 17:17

>Dunque per concludere:
>
>Utilizzando le SP se hai bisogno di fare 30 interoorgazioni sulla
>stessa tabella una, ad esempio, per ogni campo della tabella,
>bisogna creare 30 SP differenti giusto?
>
>E' questa la strada da percorrere e la migliore?
E' una delle strade, altre 2 le trovi in questo thread:
http://groups.google.it/group/microsoft.public.it.sql/browse_thread/thread/59359884f3bc01d8/

>Grazie
Prego.

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

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 17:26

Ok grazie ma adesso mi sto accorgendo che la seguente SP non agisce come deve, e cioè non prende affatto in considerazione l'operatore Like "%"

ALTER PROCEDURE [dbo].[sp_TrovaArticoliByCodice]
@CodiceArticolo nvarchar = ''
AS
BEGIN

-- Insert statements for procedure here
set lock_timeout 3000
SELECT *
from Articoli
where CodiceArticolo like @CodiceArticolo+'%';
END

Cosa sbaglio?

Ciao e GRAZIE!!

QUESTA la ho risolta!!!!

Torniamo a quella che mi dice che c'è un errore vicino a '%'

Riccardo D'Aria

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 mar 2010 - 17:33

>Cosa sbaglio?
Non saprei, posta le info che ti ho chiesto che ci ragioniamo su.

>Ciao e GRAZIE!!
Prego.

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

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 17:54

ALTER PROCEDURE [dbo].[sp_TrovaArticoliByCodice]
@CodiceArticolo nvarchar (20)
AS
declare @strsql varchar(255)
BEGIN

set @strsql = 'SELECT * FROM Articoli Where codiceArticolo LIKE ' + @CodiceArticolo+'%'
exec( @strsql)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
--set lock_timeout 3000
--SELECT *
--from Articoli
--where CodiceArticolo like @CodiceArticolo + '%';
END

CREATE TABLE [dbo].[Articoli](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IdCategoria] [int] NOT NULL,
[IdSubCategoria] [int] NOT NULL,
[CodiceArticolo] [nvarchar](20) NULL,
[nome] [nvarchar](100) NOT NULL,
[Descrizione] [nvarchar](250) NULL,
[pathImmagine] [nvarchar](100) NULL,
[UnitaMisura] [nvarchar](50) NOT NULL,
[PrezzoAcquisto] [decimal](18, 6) NULL,
[Ricarico] [float] NULL,
[PrezzoVendita] [decimal](18, 6) NULL,
[IVA] [nvarchar](20) NULL,
[attr1] [nvarchar](40) NULL,
[attr2] [nvarchar](40) NULL,
[attr3] [nvarchar](40) NULL,
[val1] [nvarchar](30) NULL,
[val2] [nvarchar](30) NULL,
[val3] [nvarchar](30) NULL,
[marca] [nvarchar](40) NULL,
[Fornitore] [nvarchar](100) NULL,
[codiceArticoloFornitore] [nvarchar](20) NULL,
[scortaMinima] [decimal](18, 0) NULL,
[qtaEsistente] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Articoli] ADD CONSTRAINT [DF_Articoli_IdCategoria] DEFAULT ((0)) FOR [IdCategoria]
GO

ALTER TABLE [dbo].[Articoli] ADD CONSTRAINT [DF_Articoli_IdSubCategoria] DEFAULT ((0)) FOR [IdSubCategoria]
GO

ALTER TABLE [dbo].[Articoli] ADD CONSTRAINT [DF_Articoli_scortaMinima] DEFAULT ((0)) FOR [scortaMinima]
GO

ALTER TABLE [dbo].[Articoli] ADD CONSTRAINT [DF_Articoli_qtaEsistente] DEFAULT ((0)) FOR [qtaEsistente]
GO

Eccoli

Riccardo D'Aria

ridaria Profilo | Expert

905 messaggi | Data Invio: gio 11 mar 2010 - 18:02

Ma la cosa bella è che questa funziona egreggiamente!

USE [DB_DATI_UBIK_STORE]
GO
/****** Object: StoredProcedure [dbo].[sp_TrovaArticoliByCodice] Script Date: 03/11/2010 18:01:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_TrovaArticoliByCodice]
@CodiceArticolo nvarchar (20)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
set lock_timeout 3000
SELECT *
from Articoli
where CodiceArticolo like @CodiceArticolo + '%';
END
Riccardo D'Aria

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 11 mar 2010 - 21:11

>Eccoli
Mancano le righe di prova (INSERT INTO), un esempio di chiamata della tua sp ed il result set atteso.

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

Cuky Profilo | Newbie

49 messaggi | Data Invio: ven 12 mar 2010 - 10:01

In realtà è possibile utilizzare la stessa S.P. per ricerche su svariati campi.

Supponiamo di passare @nome e @cognome come parametri. Se vuoi effettuare la ricerca per @nome gli passi il valore altrimenti gli passi null e nella S.P utilizzi una sintassi di questo tipo:

where (nome = @nome or @nome is null) and (cognome = @cognome or @cognome is null)

Per le performance di questo approccio rimando a Lorenzo Benaglia.
Fatti pagare per giocare a Poker On-Line
http:\\www.rakebackitalia.net

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: ven 12 mar 2010 - 10:04

>where (nome = @nome or @nome is null) and (cognome = @cognome
>or @cognome is null)
>
>
>Per le performance di questo approccio rimando a Lorenzo Benaglia.
Sono pessime

La soluzione di Itzik e quella di Marcello suggerite in uno dei precedenti link risolvono in maniera efficace il problema evitando al contempo attacchi di SQL Injection.

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

ridaria Profilo | Expert

905 messaggi | Data Invio: ven 12 mar 2010 - 16:10

>where (nome = @nome or @nome is null) and (cognome = @cognome
>or @cognome is null)
>
Sulle performance ti ha già risposto Lorenzo.

Sulla funzionalità operativa ti dico che scrivere la tua clausola where su circa 30 campi è da pazzi:

Immagina 30 "Or" per 2 e sono circa 60 scritture relative allostesso campo!!!!!!!!!
Ottimo se vuoi impazzire non appena ti restituisce un errore di sintassi SQL!

Grazie comunquwe

Riccardo D'Aria

Torna su

Stanze Forum

Elenco Threads

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 !