stored - procedimiento almacenado sql server insert

SQL Server trunca silenciosamente varchar en procedimientos almacenados

Question

De acuerdo con esta discusión en el foro , SQL Server (estoy usando 2005, pero entiendo que esto también se aplica a 2000 y 2008) trunca silenciosamente cualquier varchar que especifique como parámetros de procedimiento almacenados a la longitud de varchar, incluso si inserta esa cadena directamente usando un INSERT causaría un error. p.ej. Si creo esta tabla:

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

luego cuando ejecuto lo siguiente:

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

Me sale un error:

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

Estupendo. Se conserva la integridad de los datos y la persona que llama lo sabe. Ahora definamos un procedimiento almacenado para insertar eso:

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

y ejecutarlo:

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

Sin errores, 1 fila afectada. Se inserta una fila en la tabla, con testStringField como ''strin''. SQL Server truncó silenciosamente el parámetro varchar del procedimiento almacenado.

Ahora, este comportamiento puede ser conveniente a veces, pero entiendo que NO HAY MANERA de apagarlo. Esto es extremadamente molesto, ya que quiero que la cosa se descargue si paso una cadena demasiado larga al procedimiento almacenado. Parece haber 2 formas de lidiar con esto.

En primer lugar, declare el parámetro @testStringField del proc almacenado como tamaño 6, y verifique si su longitud es superior a 5. Esto parece una especie de hack e implica cantidades irritantes de código repetitivo.

En segundo lugar, simplemente declare TODOS los parámetros varchar del procedimiento almacenado para que varchar(max) , y luego deje que la INSERT dentro del procedimiento almacenado falle.

El último parece funcionar bien, entonces mi pregunta es: ¿es una buena idea usar varchar(max) ALWAYS para cadenas en procedimientos almacenados de SQL Server, si realmente quiero que el proc almacenado falle cuando pasa una cadena? ¿Podría ser una buena práctica? El truncamiento silencioso que no se puede desactivar me parece estúpido.

Answer 1

Actualización: me temo que esta técnica no es consistente. Pruebas adicionales revelan que a veces puedo activar el error 8152 y, en ocasiones, los datos se truncan en silencio. Estaría muy agradecido si alguien pudiera ayudarme a encontrar una forma más confiable de lidiar con esto.

Esto probablemente se está produciendo porque el 256º carácter de la cadena es un espacio en blanco. VARCHAR s truncará el espacio en blanco al insertar y solo generará una advertencia. Por lo tanto, su procedimiento almacenado está truncando silenciosamente sus cadenas a 256 caracteres, y su inserción está truncando el espacio en blanco final (con una advertencia). Producirá un error cuando dicho carácter no sea un espacio en blanco.

Quizás una solución sería hacer que el VARCHAR del procedimiento almacenado sea una longitud adecuada para capturar un carácter que no sea de espacio en blanco. VARCHAR(512) probablemente sea lo suficientemente seguro.

Answer 2

El mismo comportamiento se puede ver aquí:

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

Mi sugerencia sería hacer que el lado de la aplicación sea responsable de validar la entrada antes de llamar al procedimiento almacenado.

Answer 3

Gracias, como siempre, a por obtener este tipo de debate en profundidad. Recientemente he estado revisando mis Procedimientos almacenados para hacerlos más sólidos usando un enfoque estándar para las transacciones y probar / atrapar bloques. No estoy de acuerdo con Joe Stefanelli en que "Mi sugerencia sería hacer responsable a la aplicación", y estoy completamente de acuerdo con Jez: "Sería mucho preferible que SQL Server verifique la longitud de la cadena". El objetivo de usar procedimientos almacenados es que están escritos en un idioma nativo de la base de datos y deben actuar como una última línea de defensa. En el lado de la aplicación, la diferencia entre 255 y 256 es solo un número insignificante, pero dentro del entorno de la base de datos, un campo con un tamaño máximo de 255 simplemente no aceptará 256 caracteres. Los mecanismos de validación de la aplicación deben reflejar el db de fondo lo mejor que puedan, pero el mantenimiento es difícil, por lo que quiero que la base de datos me proporcione buenos comentarios si la aplicación permite erróneamente datos inadecuados. Es por eso que estoy usando una base de datos en lugar de un montón de archivos de texto con CSV o JSON o lo que sea.

Me sorprendió por qué uno de mis SP arrojó el error 8152 y otro truncado en silencio. Finalmente twigged: El SP que arrojó el error 8152 tenía un parámetro que permitía un carácter más que la columna de la tabla relacionada. La columna de la tabla se configuró en nvarchar (255) pero el parámetro fue nvarchar (256). Entonces, ¿no podría mi "error" abordar la preocupación de GBN: "problema de rendimiento masivo"? En lugar de usar max, quizás podríamos establecer consistentemente el tamaño de la columna de la tabla, por ejemplo, 255 y el parámetro SP en solo un carácter más largo, digamos 256. Esto resuelve el problema de truncamiento silencioso y no incurre en ninguna penalización de rendimiento. Presumiblemente hay otra desventaja en la que no he pensado, pero parece un buen compromiso para mí.

Actualización: me temo que esta técnica no es consistente. Pruebas adicionales revelan que a veces puedo activar el error 8152 y, en ocasiones, los datos se truncan en silencio. Estaría muy agradecido si alguien pudiera ayudarme a encontrar una forma más confiable de lidiar con esto.

Actualización 2: ver la respuesta de Pyitoechito en esta página.

Answer 4

Siempre puedes arrojar una declaración if en tus sp''s que verifique su longitud, y si son mayores que la longitud especificada arroja un error. Sin embargo, esto es bastante lento y sería una molestia actualizar si actualiza el tamaño de los datos.

Answer 5

Simplemente es .

Sin embargo, nunca me he dado cuenta de un problema porque uno de mis controles sería garantizar que mis parámetros coincidan con las longitudes de columna de mi tabla. En el código del cliente también. Personalmente, esperaría que SQL nunca viera datos demasiado largos. Si veo datos truncados, sangrará obvio qué lo causó.

Si sientes la necesidad de varchar (max) ten cuidado con un problema de rendimiento masivo debido a la precedencia de tipo de datos . varchar (max) tiene una precedencia mayor que varchar (n) (la más larga es la más alta). Por lo tanto, en este tipo de consulta obtendrá un escaneo, no una búsqueda, y cada valor de varchar (100) es CAST a varchar (max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Editar:

Hay un artículo abierto de Microsoft Connect con respecto a este problema.

Y probablemente valga la pena incluirlo en la configuración estricta de Erland Sommarkog (y en el elemento Connect correspondiente ).

Edite 2, después de comentar 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;

Answer 6

Una solución sería:

1. Cambie todos los parámetros entrantes para ser varchar(max)
2. Tener sp variable privada de la longitud de datos correcta (simplemente copie y pegue todo en los parámetros y agregue "int" al final
3. Declare una variable de tabla con los nombres de columna igual que los nombres de variable
4. Inserta en la tabla una fila donde cada variable entra en la columna con el mismo nombre
5. Seleccione de la tabla en variables internas

De esta forma, sus modificaciones al código existente serán mínimas, como en el ejemplo siguiente.

Este es el código original:

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

Este es el nuevo código:

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

Tenga en cuenta que si la longitud de los parámetros entrantes va a ser mayor que el límite en lugar de cortar silenciosamente la cadena SQL Server arrojará un error.