[SOLUCIONADO] Sql server dinámico

[loser213]

Señores, de antemano gracias por la ayuda que me puedan brindar. Soy nuevo en el foro y mi consulta es la siguiente, estoy creando una query dinámicamente en un procedimiento, a continuación dejo el código:

Código SQL:

ALTER PROCEDURE [dbo].[SEL_USUARIO_BY_SEARCH] @Departamento NVARCHAR(50), @Usuario NVARCHAR(50), 
@Nombre NVARCHAR(128) AS 
    DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT U.IdUsuario, U.Departamento, D.NombreDepartamento, U.NickUsuario, U.ContrasegnaUsuario, 
        U.NombresUsuario + '' '' + U.PrimerApellidoUsuario + '' '' + U.SegundoApellidoUsuario Usuario, 
        U.EmailUsuario, U.TelefonoUsuario, CONVERT(VARCHAR, U.FechaIngresoUsuario, 103) FechaIngreso, 
        CONVERT(VARCHAR, U.UltimoIngresoUsuario, 103) UltimoIngreso, U.ImagenUsuario 
        FROM Usuarios U INNER JOIN Departamentos D ON U.Departamento = D.IdDepartamento 
        WHERE 1 = 1'
    IF @Departamento IS NOT NULL AND @Departamento <> ''
    BEGIN
        SET @SQL = @SQL + ' AND (D.NombreDepartamento LIKE ''%\' + Quotename(@Departamento,'''') + '%'' ESCAPE ''\'' 
        OR D.NombreDepartamento LIKE ''%_' + Quotename(@Departamento,'''') + '%'' ESCAPE ''_'')'
    END
    IF @Usuario IS NOT NULL AND @Usuario <> ''
    BEGIN
        SET @Sql = @Sql + ' AND (U.NickUsuario LIKE ''%\' + Quotename(@Usuario,'''') + '%'' ESCAPE ''\'' 
        OR U.NickUsuario LIKE ''%_' + Quotename(@Usuario,'''') + '%'' ESCAPE ''_'')'
    END
    IF @Nombre IS NOT NULL AND @Nombre <> ''
    BEGIN
        SET @Sql = @Sql + ' AND (U.NombresUsuario' + ' ' + 'U.PrimerApellidoUsuario' + ' ' + 'U.SegundoApellidoUsuario LIKE ''%\' + Quotename(@Nombre,'''') + '%'' ESCAPE ''\'' 
        OR U.NombresUsuario' + ' ' + 'U.PrimerApellidoUsuario' + ' ' + 'U.SegundoApellidoUsuario LIKE ''%_' + Quotename(@Nombre,'''') + '%'' ESCAPE ''_'')'
    END
    SET @Sql = @Sql + ' ORDER BY U.NickUsuario ASC'
    BEGIN TRAN
        PRINT @Sql
        --EXEC(@Sql)
    COMMIT TRAN
END
 

Al ejecutar (por ejemplo EXEC SEL_USUARIO_BY_SEARCH 'P', 'r', 'r') el resultado es el siguiente:

Código SQL:

SELECT U.IdUsuario, U.Departamento, D.NombreDepartamento, U.NickUsuario, U.ContrasegnaUsuario, 
U.NombresUsuario + ' ' + U.PrimerApellidoUsuario + ' ' + U.SegundoApellidoUsuario Usuario, 
U.EmailUsuario, U.TelefonoUsuario, CONVERT(VARCHAR, U.FechaIngresoUsuario, 103) FechaIngreso, 
CONVERT(VARCHAR, U.UltimoIngresoUsuario, 103) UltimoIngreso, U.ImagenUsuario 
FROM Usuarios U INNER JOIN Departamentos D ON U.Departamento = D.IdDepartamento 
WHERE 1 = 1 AND (D.NombreDepartamento LIKE '%\'P'%' ESCAPE '\' 
OR D.NombreDepartamento LIKE '%_'P'%' ESCAPE '_') AND (U.NickUsuario LIKE '%\'r'%' ESCAPE '\' 
OR U.NickUsuario LIKE '%_'r'%' ESCAPE '_') AND (U.NombresUsuario U.PrimerApellidoUsuario U.SegundoApellidoUsuario LIKE '%\'r'%' ESCAPE '\' 
OR U.NombresUsuario U.PrimerApellidoUsuario U.SegundoApellidoUsuario LIKE '%_'r'%' ESCAPE '_') ORDER BY U.NickUsuario ASC
 

Se puede observar que la consulta es correcta, con la excepción de que los parámetros enviados quedan entre comillas, por lo cual nunca me dará resultado alguno.

Ojalá se pueda solucionar. Gracias.

[Libras]

pregunta para que es el ESCAPE??? y para que usas el quotename??

prueba con esto:

Código SQL:

ALTER PROCEDURE [dbo].[SEL_USUARIO_BY_SEARCH] @Departamento NVARCHAR(50), @Usuario NVARCHAR(50), 
@Nombre NVARCHAR(128) AS 
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT U.IdUsuario, U.Departamento, D.NombreDepartamento, U.NickUsuario, U.ContrasegnaUsuario, 
U.NombresUsuario + '' '' + U.PrimerApellidoUsuario + '' '' + U.SegundoApellidoUsuario Usuario, 
U.EmailUsuario, U.TelefonoUsuario, CONVERT(VARCHAR, U.FechaIngresoUsuario, 103) FechaIngreso, 
CONVERT(VARCHAR, U.UltimoIngresoUsuario, 103) UltimoIngreso, U.ImagenUsuario 
FROM Usuarios U INNER JOIN Departamentos D ON U.Departamento = D.IdDepartamento 
WHERE 1 = 1'
IF @Departamento IS NOT NULL AND @Departamento <> ''
BEGIN
SET @SQL = @SQL + ' AND (D.NombreDepartamento LIKE ''%\' + @Departamento + '%'' ESCAPE ''\'' 
OR D.NombreDepartamento LIKE ''%_' + @Departamento + '%'' ESCAPE ''_'')'
END
IF @Usuario IS NOT NULL AND @Usuario <> ''
BEGIN
SET @Sql = @Sql + ' AND (U.NickUsuario LIKE ''%\' + @Usuario + '%'' ESCAPE ''\'' 
OR U.NickUsuario LIKE ''%_' + @Usuario + '%'' ESCAPE ''_'')'
END
IF @Nombre IS NOT NULL AND @Nombre <> ''
BEGIN
SET @Sql = @Sql + ' AND (U.NombresUsuario' + ' ' + 'U.PrimerApellidoUsuario' + ' ' + 'U.SegundoApellidoUsuario LIKE ''%\' + @Nombre + '%'' ESCAPE ''\'' 
OR U.NombresUsuario' + ' ' + 'U.PrimerApellidoUsuario' + ' ' + 'U.SegundoApellidoUsuario LIKE ''%_' + @Nombre + '%'' ESCAPE ''_'')'
END
SET @Sql = @Sql + ' ORDER BY U.NickUsuario ASC'
BEGIN TRAN
PRINT @Sql
--EXEC(@Sql)
COMMIT TRAN
END
 

[loser213]

Iniciado por Libras

pregunta para que es el ESCAPE??? y para que usas el quotename??

prueba con esto:

Código SQL:

ALTER PROCEDURE [dbo].[SEL_USUARIO_BY_SEARCH] @Departamento NVARCHAR(50), @Usuario NVARCHAR(50), 
@Nombre NVARCHAR(128) AS 
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT U.IdUsuario, U.Departamento, D.NombreDepartamento, U.NickUsuario, U.ContrasegnaUsuario, 
U.NombresUsuario + '' '' + U.PrimerApellidoUsuario + '' '' + U.SegundoApellidoUsuario Usuario, 
U.EmailUsuario, U.TelefonoUsuario, CONVERT(VARCHAR, U.FechaIngresoUsuario, 103) FechaIngreso, 
CONVERT(VARCHAR, U.UltimoIngresoUsuario, 103) UltimoIngreso, U.ImagenUsuario 
FROM Usuarios U INNER JOIN Departamentos D ON U.Departamento = D.IdDepartamento 
WHERE 1 = 1'
IF @Departamento IS NOT NULL AND @Departamento <> ''
BEGIN
SET @SQL = @SQL + ' AND (D.NombreDepartamento LIKE ''%\' + @Departamento + '%'' ESCAPE ''\'' 
OR D.NombreDepartamento LIKE ''%_' + @Departamento + '%'' ESCAPE ''_'')'
END
IF @Usuario IS NOT NULL AND @Usuario <> ''
BEGIN
SET @Sql = @Sql + ' AND (U.NickUsuario LIKE ''%\' + @Usuario + '%'' ESCAPE ''\'' 
OR U.NickUsuario LIKE ''%_' + @Usuario + '%'' ESCAPE ''_'')'
END
IF @Nombre IS NOT NULL AND @Nombre <> ''
BEGIN
SET @Sql = @Sql + ' AND (U.NombresUsuario' + ' ' + 'U.PrimerApellidoUsuario' + ' ' + 'U.SegundoApellidoUsuario LIKE ''%\' + @Nombre + '%'' ESCAPE ''\'' 
OR U.NombresUsuario' + ' ' + 'U.PrimerApellidoUsuario' + ' ' + 'U.SegundoApellidoUsuario LIKE ''%_' + @Nombre + '%'' ESCAPE ''_'')'
END
SET @Sql = @Sql + ' ORDER BY U.NickUsuario ASC'
BEGIN TRAN
PRINT @Sql
--EXEC(@Sql)
COMMIT TRAN
END
 

Estimado, muchas gracias, funcionó perfecto. Efectivamente el problema estaba en Quotename el cual lo estaba utilizando para generación de comillas en una consulta anterior:

Código SQL:

DECLARE @SQL NVARCHAR(500)
    SET @SQL = 'SELECT * FROM Usuarios WHERE 1 = 1'
    IF @Usuario IS NOT NULL AND @Usuario <> ''
    BEGIN
        SET @SQL = @SQL + ' AND NickUsuario = ' + Quotename(@Usuario,'''') + ''
    END
    EXEC(@SQL)
 

al no colocar Quotename, si ejecuto EXEC Search 'pp', '' la consulta me devuelve SELECT * FROM Usuarios WHERE 1 = 1 AND NickUsuario = [pp]

El escape lo utilizo por motivos de seguridad http://mteheran.wordpress.com/2010/0...on-sql-server/ en ese link se explica el por qué.

Muchas gracias nuevamente Libras.

[Libras]

creo que la parte de escape seria mejor manejarla desde la aplicacion, desde ahi validar que lleguen los datos limpios, aunque nunca esta de mas una doble checada :P

[loser213]

Es justamente lo que hago siempre con todos los lenguajes y motores de bdd, no quedarme solamente con la chequeada de un solo lado, aunque parezca exagerado.

[Libras]

muy buena practica, aunque creo que es demasiada paranoia :P jejejejeje