Trabajando con cadenas de texto

On 30 mayo, 12:51, JuanMa wrote:

Hola,
mando a un store procedure un nvarchar que contiene números separados
por coma :'4,5,16,3,45' .
El problema viene cuando intento en la Select meterlo dentro de un
WHERE nombre columna IN () . Me dice que no puede convertir un
nvarchar en un INT.

El ejemplo de abajo me ha funcionado(os paso tb el que no funciona) ,
pero el nvarchar solamente trae dos numeros separados por comas, el
problema es que hay veces que necesitaré mandar hasta 25 valores.
¿Se os ocurre algo?

NO FUNCIONA
declare
@id_areas nvarchar(1000),

BEGIN

SELECT @id_areas='3,5'

SELECT T_Usuario.id_usuario, T_Usuario.usu_codigo,
T_Usuario.usu_nombre, T_Usuario.usu_apellidos,
T_Usuario.usu_descripcion,T_Usuario.usu_estado,T_Usuario.usu_email,
T_Usuario.usu_entidad
FROM T_Usuario INNER JOIN T_NMP_Usuario_Perfil_Entidad ON
T_Usuario.id_usuario = T_NMP_Usuario_Perfil_Entidad.id_usuario
WHERE T_NMP_Usuario_Perfil_Entidad.id_area_entidad IN (@id_areas)

end

FUNCIONA
declare
@id_areas nvarchar(1000),
@id1 int,
@id2 int

BEGIN

SELECT @id_areas='3,5'
Select @id1 = CAST(SUBSTRING(@id_areas, 1, 1)AS INT)
SELECT @id2 = CAST(SUBSTRING(@id_areas, 3, 1)AS INT)

SELECT T_Usuario.id_usuario, T_Usuario.usu_codigo,
T_Usuario.usu_nombre, T_Usuario.usu_apellidos,
T_Usuario.usu_descripcion,T_Usuario.usu_estado,T_Usuario.usu_email,
T_Usuario.usu_entidad
FROM T_Usuario INNER JOIN T_NMP_Usuario_Perfil_Entidad ON
T_Usuario.id_usuario = T_NMP_Usuario_Perfil_Entidad.id_usuario
WHERE T_NMP_Usuario_Perfil_Entidad.id_area_entidad IN (@id1,@id2)

end

AQUI HE DADO CON LA SOLUCIÓN



CREATE PROCEDURE dbo.pa_t_usuarios_leer
@usu_nombre nvarchar(100) = '',
@tipo_filtro int=0,
@id_areas nvarchar(1000)=NULL

AS

IF @tipo_filtro = 0
BEGIN
declare @consulta varchar(1000)

select @consulta = 'SELECT id_usuario, usu_codigo, usu_nombre,
usu_apellidos,
usu_descripcion, usu_estado,usu_email, usu_entidad
FROM T_Usuario
WHERE (usu_nombre like ''%' + @usu_nombre + '%'') or (usu_apellidos
like ''%' + @usu_nombre + '%'')'

END

else

if @tipo_filtro =1
BEGIN

select @consulta = 'SELECT T_Usuario.id_usuario,
T_Usuario.usu_codigo, T_Usuario.usu_nombre, T_Usuario.usu_apellidos,
T_Usuario.usu_descripcion, T_Usuario.usu_estado,
T_Usuario.usu_email, T_Usuario.usu_entidad
FROM T_Usuario INNER JOIN T_NMP_Usuario_Perfil_Entidad ON
T_Usuario.id_usuario = T_NMP_Usuario_Perfil_Entidad.id_usuario
WHERE (T_NMP_Usuario_Perfil_Entidad.id_area_entidad IN (' +
@id_areas + ')) AND
( (T_Usuario.usu_apellidos LIKE ''%' + @usu_nombre +
'%'') OR (T_Usuario.usu_nombre LIKE ''%' + @usu_nombre + '%''))'

END

exec (@consulta)
GO

Un par de cosillas

1) ¿De verdad es necesario que la búsqueda en los nombres sea "que contenga
la cadena especificada"? Una búsqueda así usando LIKE '%La cadena%' siempre
provoca que SQL Server lea la tabla entera, los índices no sirven de nada.
Yo creo que la mayoría de las veces es suficiente con que la búsqueda sea
"que empiece por la cadena especificada" usando LIKE 'La cadena%'. Así SQL
Server sí que podrá utilizar los índices si los tiene, y la consulta podrá
ser mucho más eficiente. Otra alternativa a LIKE '%La cadena%' es usar Full
Text Search.


2) La consulta con @tipo_filtro = 1 en la que usas ejecución dinámica, estás
construyendo la instrucción sql concatenando con los valores de los
parámetros, lo cual la hace vulnerable a inyección SQL. Además la consulta
resultante es una consulta con literales en vez de parámetros con lo que SQL
Server no reutiliza el plan de ejecución, lo que es poco eficiente. Yo te
propongo manipular @id_areas para convertilo en un documento XML y usar
OPENXML si es SQL 2000 o nodes-value si es SQL 2005 para el IN. De esta
manera no se usa ejecución dinámica que en este caso es peligrosa. Aquí lo
tienes usando OPENXML:


IF @tipo_filtro =1
BEGIN

DECLARE @xml nvarchar(4000)
SET @xml = N'<ns><n>' + REPLACE(@id_areas , N',', N'</n><n>') +
N'</n></ns>'
DECLARE @xmlDoc int
EXEC sp_xml_preparedocument @xmlDoc OUT, @xml

SELECT U.id_usuario,
U.usu_codigo,
U.usu_nombre,
U.usu_apellidos,
U.usu_descripcion,
U.usu_estado,
U.usu_email,
U.usu_entidad
FROM T_Usuario U INNER JOIN T_NMP_Usuario_Perfil_Entidad P
ON U.id_usuario = P.id_usuario
WHERE P.id_area_entidad IN ( SELECT n
FROM OPENXML
(@xmlDoc, '/ns/n')
WITH
(n int '.')
)
AND ( U.usu_apellidos LIKE '%' + @usu_nombre + '%' OR
U.usu_nombre LIKE '%' + @usu_nombre + '%')

EXEC sp_xml_removedocument @xmlDoc

END


Saludos:


Jesús López
www.solidq.com