Error in script to run in both mssql version 2014 and 2017

Khan Consultoria
Khan Consultoria used Ask the Experts™
on
Hello guys,

I need help with my script.

I have a script that must be run in MSQL 2014 e MSSQ2017

The problem is the syntax of error, how to work around with this?

the error:

Msg 102, Level 15, State 1, Procedure td_ensespecialidades, Line 64
Sintaxe incorreta próxima a '@errno'.

if exists (select name from sysobjects where name = 'td_ensespecialidades') 
  drop trigger td_ensespecialidades;
go


exec('create trigger td_ensespecialidades on ensEspecialidades for delete as
begin
    declare
       @numrows  int,
       @errno    int,
       @errmsg   varchar(255)

    select  @numrows = @@rowcount
    if @numrows = 0
       return

    /*  DeleteParentRestrict: Impossivel excluir pai "ensEspecialidades" pois existem registros na filha "ensGrades_Profissionais"  */
    if exists (select 1 from ensGrades_Profissionais t2, deleted t1
               where  t2.especialidade = t1.especialidade)
       begin
          select @errno  = 50006,
                 @errmsg = ''Existem registros em "ensGrades_Profissionais". Impossível excluir "ensEspecialidades".''
          goto error
       end

    /*  DeleteParentCascade: apaga todas as filhas "ensPolos_Especialidades"  da Pai "ensEspecialidades"  */
    set nocount on

    delete ensPolos_Especialidades
      from   ensPolos_Especialidades t2, deleted t1
      where  t2.especialidade = t1.especialidade

    set nocount off

    /*  DeleteParentCascade: apaga todas as filhas "ensEspecialidades_Profissionais"  da Pai "ensEspecialidades"  */
    set nocount on

    delete ensEspecialidades_Profissionais
      from   ensEspecialidades_Profissionais t2, deleted t1
      where  t2.especialidade = t1.especialidade

    set nocount off

    /*  DeleteParentCascade: apaga todas as filhas "ensRegioesAdm_ValoresPadrao"  da Pai "ensEspecialidades"  */
    set nocount on

    delete ensRegioesAdm_ValoresPadrao
      from   ensRegioesAdm_ValoresPadrao t2, deleted t1
      where  t2.especialidade = t1.especialidade

    set nocount off

    /*  DeleteParentSetNull: Exclusão de registro na pai "ensEspecialidades" deixa null a chave na filha "ensProfissionais"  */
    set nocount on

    update ensProfissionais
     set   especialidade_principal = NULL
    from   ensProfissionais t2, deleted t1
    where  t2.especialidade_principal = t1.especialidade

    set nocount off

    return

error:
    raiserror @errno @errmsg
    rollback  transaction
end
')
----------------------------------------

Open in new window


Thanks a lot for help
Alex
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>    raiserror @errno @errmsg
My knee-jerk reaction is RAISERROR requires parentheses marks around the parameters and commas separating them.

raiserror (@errno, @errmsg)

Open in new window

>I have a script that must be run in MSQL 2014 e MSSQ2017
If this means 'I have a script that needs to run in both 2014 and 2017', then that is not an abundantly realistic expectation, as Microsoft makes no promises that T-SQL scripts are backwards compatible.

>exec('create trigger td_ensespecialidades on ensEspecialidades for delete as
As an aside, what's the purpose of doing this as dynamic sql with the exec (..) ?  I don't see any variable replacement in the string, so you should be able to just execute the CREATE TRIGGER.

Author

Commented:
Thanks a lot for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial