Stored Procedure + nomi di campi dinamici

giovedì 11 marzo 2010 - 11.24

ridaria Profilo | Expert

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

>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


>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

>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

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

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

>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

>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

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

>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

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

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

>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

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

>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


>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
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-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5