хранившаяся процедура sql server

Цель состоит в том, чтобы реализовывать store procedure, который консультировал бы все ресурсы, но применяя от одного до пять фильтров в неударе в лунку, а именно, пользователь может искать по имени, hashtag, ключ, имя типа ресурса, имя площади знания,

У меня есть следующая диаграмма:

esta es la relación de mis tablas, ya implemente el store y si funciona en los filtros que pertenecen a la tabla recurso, pero ahora quiero que también funcione en la tabla tipo de recurso y área de conocimiento

Это мой код:

USE SIRH

--Si ya existe el procedimiento almacenado se descarta
IF EXISTS (SELECT * FROM sys.objects WHERE TYPE = 'P' AND NAME='SPBuscarRecurso')
BEGIN
    DROP PROCEDURE [dbo].[SPBuscarRecurso];
    PRINT 'El procedimiento almacenado [dbo].[SPBuscarRecurso] se elimino correctamente';
END

--Si no existe, entonces se crea 
GO
CREATE PROCEDURE [dbo].[SPBuscarRecurso]
(@cClave VARCHAR(MAX), @cNombre varchar(max), @cHashtag VARCHAR(MAX),
@cNombreAreaConocimiento VARCHAR(MAX), @cNombreTipoRecurso VARCHAR (MAX))
WITH 
EXECUTE AS CALLER
AS

    BEGIN TRY
    BEGIN TRANSACTION

         --Se verifica que la variable nombre no este vacia
        IF(@cNombre != '' or @cClave != '' or @cHashtag != '' or @cNombreTipoRecurso != '' or @cNombreAreaConocimiento != '') --si es asi, entonces hace la consulta 
        BEGIN

             SELECT DISTINCT * FROM tbl_Recurso  join       --se hace el join con la tabla tipo de recurso y Area de conocimiento
             tblCat_TipoRecurso on tblCat_TipoRecurso.cNombre LIKE 
             '%'+  @cNombreTipoRecurso +'%' join        --se compara el nombre con valor del parametro que recibe
             tblCat_AreaConocimiento on tblCat_AreaConocimiento.cNombre LIKE
             '%'+ @cNombreAreaConocimiento + '%' where
             tbl_Recurso.iIdTipoRecurso = tblCat_TipoRecurso.iIdTipoRecurso and 
             tbl_Recurso.iIdAreaConocimiento = tblCat_AreaConocimiento.iIdAreaConocimiento or
                (tbl_Recurso.cNombre LIKE '%'+ @cNombre +'%' ) or
                (tbl_Recurso.cClave Like '%' + @cClave + '%') or
                (tbl_Recurso.cHashtag LIKE'%'+  @cHashtag + '%')

        END

        Else begin

        PRINT 'Registros no encontrados'

END
--Si todo esta correcto se hace el commit
COMMIT TRANSACTION
PRINT 'Transaccion realizada con exito'

END TRY

--EXcepciones y control de errores
BEGIN CATCH
   DECLARE @Error_Number int, @Error_Severity int, @Error_State int,
        @Error_Procedure varchar(1000), @Error_Line int, @Error_Message varchar(8000)
    SELECT
        @Error_Number = ERROR_NUMBER(),
        @Error_Severity = ERROR_SEVERITY(),
        @Error_State = ERROR_STATE(),
        @Error_Procedure = ERROR_PROCEDURE(),
        @Error_Line = ERROR_LINE(),
        @Error_Message = ERROR_MESSAGE()

    RAISERROR(@Error_Message,@Error_Severity, @Error_State) 

    PRINT 'Ha ocorrido un error. No se realizo la transacción'

    IF @@TRANCOUNT > 0 
        ROLLBACK TRANSACTION

END CATCH
GO
 PRINT 'El procedimiento almacenado [dbo].[SPBuscarRecurso] fue creado correctamente';

1
задан 06.11.2019, 18:15
3 ответа

У тебя есть несколько проблем aquГ-. EstГЎs путая статьи WHERE со статьями JOIN, из-за которого estГЎn вложенные. TambiГ©n estГЎs используя varchar (max), что безопасное но медленное. Всегда ты должен использовать тип подходящей информации. Твоя консультация, - если они "содержат" algГєn стоимость, что harГЎ медленная консультация потому что когда бы то ни было podrГЎ использовать Г-ndices. Из-за Гєltimo, ты нуждаешься в том, чтобы улучшить условия, чтобы мочь получать sГіlo линии (не реестры), который выполняет какую-то condiciГіn.

USE SIRH

--Si ya existe el procedimiento almacenado se descarta
IF EXISTS (SELECT * FROM sys.objects WHERE TYPE = 'P' AND NAME='SPBuscarRecurso')
BEGIN
    DROP PROCEDURE [dbo].[SPBuscarRecurso];
    PRINT 'El procedimiento almacenado [dbo].[SPBuscarRecurso] se elimino correctamente';
END

--Si no existe, entonces se crea 
GO
CREATE PROCEDURE [dbo].[SPBuscarRecurso]
(@cClave VARCHAR(MAX), @cNombre varchar(max), @cHashtag VARCHAR(MAX),
@cNombreAreaConocimiento VARCHAR(MAX), @cNombreTipoRecurso VARCHAR (MAX))
WITH 
EXECUTE AS CALLER
AS

BEGIN TRY
    BEGIN TRANSACTION;

    --Se verifica que la variable nombre no este vacia
    IF(@cNombre != '' or @cClave != '' or @cHashtag != '' or @cNombreTipoRecurso != '' or @cNombreAreaConocimiento != '') --si es asi, entonces hace la consulta 
    BEGIN

        SELECT DISTINCT * 
        FROM  tbl_Recurso               r
        JOIN  tblCat_TipoRecurso        tr ON r.idTipoRecurso       = tr.IdTipoRecurso
        JOIN  tblCat_AreaConocimiento   ac ON r.iIdAreaConocimiento = ac.iIdAreaConocimiento
        WHERE (r.cNombre  LIKE '%' + @cNombre +'%'                  OR @cNombre                 IS NULL OR @cNombre                 = '')
        AND   (r.cClave   LIKE '%' + @cClave + '%'                  OR @cClave                  IS NULL OR @cClave                  = '')
        AND   (r.cHashtag LIKE '%' + @cHashtag + '%'                OR @cHashtag                IS NULL OR @cHashtag                = '')
        AND   (tr.cNombre LIKE '%' + @cNombreTipoRecurso +'%'       OR @cNombreTipoRecurso      IS NULL OR @cNombreTipoRecurso      = '')
        AND   (ac.cNombre LIKE '%' + @cNombreAreaConocimiento +'%'  OR @cNombreAreaConocimiento IS NULL OR @cNombreAreaConocimiento = '')
        OPTION(RECOMPILE);
    END
    ELSE 
    BEGIN
        PRINT 'Registros no encontrados';
    END

    --Si todo esta correcto se hace el commit
    COMMIT TRANSACTION;
    PRINT 'Transaccion realizada con exito';

END TRY

--EXcepciones y control de errores
BEGIN CATCH
   DECLARE @Error_Number int, @Error_Severity int, @Error_State int,
        @Error_Procedure varchar(1000), @Error_Line int, @Error_Message varchar(8000)
    SELECT
        @Error_Number = ERROR_NUMBER(),
        @Error_Severity = ERROR_SEVERITY(),
        @Error_State = ERROR_STATE(),
        @Error_Procedure = ERROR_PROCEDURE(),
        @Error_Line = ERROR_LINE(),
        @Error_Message = ERROR_MESSAGE()

    RAISERROR(@Error_Message,@Error_Severity, @Error_State) 

    PRINT 'Ha ocorrido un error. No se realizo la transacción'

    IF @@TRANCOUNT > 0 
        ROLLBACK TRANSACTION

END CATCH
GO
 PRINT 'El procedimiento almacenado [dbo].[SPBuscarRecurso] fue creado correctamente';
0
ответ дан 01.12.2019, 12:33
  • 1
    Луис Гонсалес спасибо, если я я функционирую – Gera96 06.11.2019, 20:30
  • 2
    ¿ И ты понимаешь cuá l был недостатком и cuá l был solució n? – Luis Cazares 06.11.2019, 20:33
  • 3
    если, равный я понял, что он помещал OR вместо AND, что вызывало, что cicle мои реестры и консультация не функционировали подходящим способом – Gera96 07.11.2019, 16:23

Я думаю, что тебе не хватает они parГ©ntesis

SELECT DISTINCT * FROM tbl_Recurso
join tblCat_TipoRecurso on tblCat_TipoRecurso.cNombre LIKE '%'+  @cNombreTipoRecurso %' join
tblCat_AreaConocimiento on tblCat_AreaConocimiento.cNombre LIKE %'+ @cNombreAreaConocimiento + '%' 
where tbl_Recurso.iIdTipoRecurso = tblCat_TipoRecurso.iIdTipoRecurso and 
tbl_Recurso.iIdAreaConocimiento = tblCat_AreaConocimiento.iIdAreaConocimiento 
or (
    (tbl_Recurso.cNombre LIKE '%'+ @cNombre +'%' ) or
    (tbl_Recurso.cClave Like '%' + @cClave + '%') or
    (tbl_Recurso.cHashtag LIKE'%'+  @cHashtag + '%')
)
0
ответ дан 01.12.2019, 12:33
  • 1
    выполнять SP: используйте SIRH EXECUTE dbo. SPBuscarRecurso cClave = ' ' cNombre = ' Test' cHashtag = ' ' cNombreAreaConocimiento = ' ' cNombreTipoRecurso = ' ' все поля пустые, кроме имя, но он это не уважает и удваивает меня или утраивает данные – Gera96 06.11.2019, 19:18

Проблема - как estГЎs выдвигая LIKE, например:

tbl_Recurso.cNombre LIKE '%'+ @cNombre +'%'

ВїQue происходит, когда @cNombre = ''? немного как это:

tbl_Recurso.cNombre LIKE '%%'

то, что он говорит ему в основание, "принеси мне все линии". Если ты хочешь решить это с '' как флаг игнорирования фильтра, что в действительности deberГ-схвати делать, это что-то как это:

(@cNombre <> '' and tbl_Recurso.cNombre LIKE '%'+ @cNombre +'%')

А именно, если parГЎmetro он не является белым, мы делаем LIKE а объявляется недействительным фильтр. Это функционирует, из-за которого она conexiГіn между фильтрами его один OR, не забывать их parГ©ntesis, что заключают and. Восток patrГіn - тот, которого deberГ-схвати, использовать во всех них demГЎs фильтры твоей консультации

0
ответ дан 01.12.2019, 12:33