SQL Server tronca silenziosamente varchar nelle stored procedure

In base a questa discussione sul forum , SQL Server (sto usando 2005 ma lo considero valido anche per il 2000 e il 2008) tronca silenziosamente tutti i varchar che specifichi come parametri della stored procedure sulla lunghezza del varchar, anche se inserisci quella stringa direttamente usando un INSERT potrebbe effettivamente causare un errore. per esempio. Se creo questa tabella:

 CREATE TABLE testTable( [testStringField] [nvarchar](5) NOT NULL ) 

quindi quando eseguo il seguente:

 INSERT INTO testTable(testStringField) VALUES(N'string which is too long') 

Ottengo un errore:

 String or binary data would be truncated. The statement has been terminated. 

Grande. L’integrità dei dati è preservata e il chiamante ne è a conoscenza. Ora definiamo una stored procedure per inserirla:

 CREATE PROCEDURE spTestTableInsert @testStringField [nvarchar](5) AS INSERT INTO testTable(testStringField) VALUES(@testStringField) GO 

ed eseguirlo:

 EXEC spTestTableInsert @testStringField = N'string which is too long' 

Nessun errore, 1 riga interessata. Una riga viene inserita nella tabella, con testStringField come ‘strin’. SQL Server ha troncato silenziosamente il parametro varchar della stored procedure.

Ora, questo comportamento potrebbe essere conveniente a volte, ma ho capito che non c’è modo di spegnerlo. Questo è estremamente fastidioso, poiché desidero che la cosa venga erroneamente se passo una stringa troppo lunga alla stored procedure. Sembra che ci siano 2 modi per affrontare questo.

In primo luogo, dichiarare il parametro @testStringField del stored proc come dimensione 6 e verificare se la sua lunghezza è superiore a 5. Questo sembra un po ‘un hack e comporta quantità irritanti di codice boilerplate.

In secondo luogo, dichiarare tutti i parametri varchar della stored procedure a varchar(max) , quindi lasciare che l’istruzione INSERT all’interno della stored procedure abbia esito negativo.

Quest’ultimo sembra funzionare bene, quindi la mia domanda è: è una buona idea usare varchar(max) SEMPRE per le stringhe nelle stored procedure di SQL Server, se davvero voglio che il proc memorizzato fallisca quando una stringa troppo lunga viene passata? Potrebbe essere anche la migliore pratica? Il troncamento silenzioso che non può essere distriggersto mi sembra stupido.

E ‘solo

Non ho mai notato un problema, però, perché uno dei miei controlli sarebbe garantire che i miei parametri corrispondessero alle lunghezze delle colonne della mia tabella. Anche nel codice cliente. Personalmente, mi aspetterei che SQL non veda mai i dati troppo lunghi. Se vedessi i dati troncati, sarebbe evidente che cosa l’ha causato.

Se si avverte la necessità di varchar (max), prestare attenzione a un enorme problema di prestazioni a causa della precedenza dei tipi di dati . varchar (max) ha una precedenza maggiore di varchar (n) (il più lungo è il più alto). Quindi in questo tipo di query otterrai una scansione non una ricerca e ogni valore varchar (100) è CAST per varchar (max)

 UPDATE ...WHERE varchar100column = @varcharmaxvalue 

Modificare:

È disponibile un elemento Microsoft Connect aperto relativo a questo problema.

Ed è probabilmente degno di inclusione nelle impostazioni Strict di Erland Sommarkog (e nella voce Connect corrispondente ).

Modifica 2, dopo il commento di Martins:

 DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX); SELECT @sql = 'B', @nsql = 'B'; SELECT LEN(@sql), LEN(@nsql), DATALENGTH(@sql), DATALENGTH(@nsql) ; DECLARE @t table(c varchar(8000)); INSERT INTO @t values (replicate('A', 7500)); SELECT LEN(c) from @t; SELECT LEN(@sql + c), LEN(@nsql + c), DATALENGTH(@sql + c), DATALENGTH(@nsql + c) FROM @t; 

Grazie, come sempre, a StackOverflow per aver suscitato questo tipo di discussione approfondita. Recentemente ho passato in rassegna le mie Stored Procedure per renderle più solide usando un approccio standard alle transazioni e provare / catturare i blocchi. Non sono d’accordo con Joe Stefanelli sul fatto che “il mio suggerimento sarebbe quello di rendere il lato dell’applicazione responsabile”, e sono pienamente d’accordo con Jez: “Avere SQL Server verificare che la lunghezza della stringa sarebbe molto preferibile”. L’intero punto per me di usare stored procedure è che sono scritte in una lingua nativa del database e dovrebbero fungere da ultima linea di difesa. Sul lato dell’applicazione la differenza tra 255 e 256 è solo un numero senza numero ma all’interno dell’ambiente di database, un campo con una dimensione massima di 255 semplicemente non accetta 256 caratteri. I meccanismi di validazione dell’applicazione dovrebbero riflettere il back-end db come meglio possono, ma la manutenzione è difficile, quindi voglio che il database mi dia un buon feedback se l’applicazione consente erroneamente di dati inadatti. Ecco perché sto usando un database invece di un mucchio di file di testo con CSV o JSON o qualsiasi altra cosa.

Ero perplesso sul motivo per cui uno dei miei SP ha gettato l’errore 8152 e un altro troncato silenziosamente. Alla fine ho twigato: l’SP che ha generato l’errore 8152 aveva un parametro che permetteva un carattere in più rispetto alla colonna della tabella correlata. La colonna della tabella era impostata su nvarchar (255) ma il parametro era nvarchar (256). Quindi, il mio “errore” non dovrebbe riguardare la preoccupazione di Gbn: “enorme problema di prestazioni”? Invece di usare max, forse potremmo impostare la dimensione della colonna della tabella, ad esempio, 255 e il parametro SP su un solo carattere più lungo, ad esempio 256. Questo risolve il problema del troncamento silenzioso e non comporta alcuna penalizzazione delle prestazioni. Presumibilmente c’è qualche altro svantaggio a cui non ho pensato, ma mi sembra un buon compromesso.

Aggiornamento: temo che questa tecnica non sia coerente. Ulteriori test rivelano che a volte posso triggersre l’errore 8152 e talvolta i dati vengono troncati silenziosamente. Sarei molto grato se qualcuno potesse aiutarmi a trovare un modo più affidabile per affrontare questo.

Aggiornamento 2: vedere la risposta di Pyitoechito su questa pagina.

Lo stesso comportamento può essere visto qui:

 declare @testStringField [nvarchar](5) set @testStringField = N'string which is too long' select @testStringField 

Il mio suggerimento sarebbe quello di rendere il lato dell’applicazione responsabile della convalida dell’input prima di chiamare la stored procedure.

Aggiornamento: temo che questa tecnica non sia coerente. Ulteriori test rivelano che a volte posso triggersre l’errore 8152 e talvolta i dati vengono troncati silenziosamente. Sarei molto grato se qualcuno potesse aiutarmi a trovare un modo più affidabile per affrontare questo.

Questo probabilmente si verifica perché il 256 ° carattere nella stringa è white-space. VARCHAR lo spazio bianco finale durante l’inserimento e genererà solo un avviso. Quindi la procedura memorizzata sta troncando silenziosamente le stringhe a 256 caratteri e l’inserimento sta troncando lo spazio bianco finale (con un avviso). Produrrà un errore quando detto carattere non è uno spazio bianco.

Forse una soluzione potrebbe essere quella di rendere il VARCHAR della stored procedure una lunghezza adeguata per catturare un carattere non di spazio bianco. VARCHAR(512) sarebbe probabilmente abbastanza sicuro.

Una soluzione potrebbe essere:

  1. Cambia tutti i parametri in entrata in varchar(max)
  2. Avere sp variabile privata della lunghezza dei dati corretta (semplicemente copia e incolla tutto nei parametri e aggiungi “int” alla fine
  3. Dichiarare una variabile di tabella con i nomi di colonna uguali ai nomi di variabili
  4. Inserisci nella tabella una riga in cui ogni variabile entra nella colonna con lo stesso nome
  5. Seleziona dalla tabella in variabili interne

In questo modo le tue modifiche al codice esistente saranno molto ridotte come nell’esempio qui sotto.

Questo è il codice originale:

 create procedure spTest ( @p1 varchar(2), @p2 varchar(3) ) 

Questo è il nuovo codice:

 create procedure spTest ( @p1 varchar(max), @p2 varchar(max) ) declare @p1Int varchar(2), @p2Int varchar(3) declare @test table (p1 varchar(2), p2 varchar(3) insert into @test (p1,p2) varlues (@p1, @p2) select @p1Int=p1, @p2Int=p2 from @test 

Si noti che se la lunghezza dei parametri in entrata sarà maggiore del limite anziché tagliare in silenzio la stringa, SQL Server genererà un errore.

Puoi sempre lanciare un’istruzione if nel tuo sp che ne controlla la lunghezza e, se sono maggiori della lunghezza specificata, genera un errore. Questo è piuttosto dispendioso in termini di tempo e sarebbe un problema da aggiornare se si aggiorna la dimensione dei dati.