Erro na geração de script de uma Procedure Numerada (Syntax error in TextHeader of NumberedStoredProcedure)

Olá pessoal tudo certo! Espero que sim!

Hoje vou comentar sobre um problema que passei recentemente envolvendo procedures numeradas (Numbered Stored Procedures), no momento em que eu tentava gerar o script delas um erro ocorria no “Wizard”.

As procedures numeradas foram criadas com o intuito de permitir ao desenvolvedor de utilizar uma mesma procedure mas com lógica e parâmetros distintos, contudo, de acordo com o Books Online esta funcionalidade será removida em versões futuras do SQL Server:


This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.


Depois desta breve explicação, vou demonstrar na prática o que são procedures numeradas e depois mostrar o problema que tive envolvendo estes tipos de procedures.

DEMO
Primeiro, vou abrir meu SQL Server 2014 e criar uma nova base de dados:

/* criando banco de testes */
create database NumberedStoredProcedures2
go

Agora vou criar uma nova procedure numerada bem simples que irá ter 4 variações:

/* criando procedures numeradas*/
create procedure Number1
as

select 1
return
go

create procedure Number1;2
as

select 2
return
go

create procedure Number1;3
as

select 3
return
go

create procedure Number1;4
as

select 4
return
go

Através de uma view de sistema implementada no SQL Server 2008 vou listar todas procedures numeradas criadas neste banco de dados:

/* consultando as procedures numeradas */
select * from sys.numbered_procedures
go



Agora, vou ver como ficou o texto da minha procedure numerada:

/* consultando o texto das procedures */
sp_helptext Number1

Notem que a procedure é uma só porém com 4 variações: 

 Text
----------------------------------------------------------------------------------------

create procedure Number1
as

select 1

return

create procedure Number1;2
as

select 2

return

create procedure Number1;3
as

select 3

return

create procedure Number1;4
as

select 4

return

Agora vou executar a procedure chamando por cada uma de suas 4 variações:

exec Number1





exec Number1;2





exec Number1;3





exec Number1;4


Agora, vou solicitar a geração do script destas procedures através do Wizard clicando com o direito em minha base de dados NumberedSotredProcedures>Tasks>Generate Scripts...



Quando o Wizard abrir clique em Next:


Vou especificar para qual procedure quero gerar o script:

Vou escolher um caminho qualquer para salvar o script e clicar em Next:
 
Depois clicarei em Next novamente:



Notem que meu script foi gerado sem problema algum:



Agora, na mesma base de dados que criei, vou apagar a procedure numerada:

/*Apagando a procedure numerada*/
drop procedure Number1
go


E vou criar ela novamente porém desta vez seu nome terá aspas duplas:

/* criando a procedure */
create procedure "Number1"
as

select 1
return
go

Notem que o SQL Server não irá permitir:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'Number1'.

Mas, habilitando o QUOTED_IDENTIFIER e rodando novamente o código, sou capaz de criar a procedure:

/*Habilitando o quoted identifier*/
set quoted_identifier on
go


/* Criando a procedure */
create procedure "Number1"
as

select 1
return
go

Resultado:



Na mesma sessão vou criar agora as variações da procedure:

/*Criando as variações da procedure*/
create procedure "Number1";2
as

select 2
return
go

create procedure "Number1";3
as

select 3
return
go

create procedure "Number1";4
as

select 4
return
go

Como fizemos anteriormente, vou consultar a view sys.numbered_procedures:

 
Vou ver o texto da procedure através do sp_helptext:


Text
----------------------------------------------------------------------------------------

create procedure "Number1"
as

select 1

return

create procedure "Number1";2
as

select 2

return

create procedure "Number1";3
as

select 3

return

create procedure "Number1";4
as

select 4

return

Agora, repetindo a etapa de geração do script das procedures veja o que acontece:


Salvando o Report do Wizard (Save Report) temos o erro abaixo:

Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Syntax error in TextHeader of NumberedStoredProcedure 'Number1;2'.
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectness(String ddlText, Boolean enforceCreate, Boolean checkName, String[] expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulateText(String ddlText, String[] expectedObjectTypes, ScriptingPreferences sp, Boolean forCreate)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextForScript(ScriptingPreferences sp, Boolean forCreate, String[] expectedObjectTypes, Boolean forceCheckNameAndManipulateIfRequired)
at Microsoft.SqlServer.Management.Smo.NumberedStoredProcedure.ScriptInternalCreateDdl(StringCollection queries, ScriptingPreferences sp, Boolean bForCreate)
at Microsoft.SqlServer.Management.Smo.NumberedStoredProcedure.ScriptInternal(StringCollection queries, ScriptingPreferences sp, Boolean bForCreate)
at Microsoft.SqlServer.Management.Smo.NumberedStoredProcedure.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.StoredProcedure.ScriptNumberedStoredProcedures(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.StoredProcedure.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)


Aparentemente o erro está do lado do SMO, que falha ao tentar validar a sintaxe da primeira procedure numerada que ele encontra. O erro também acontece quando eu clico com o direito na procedure e peço seu CREATE TO ou DROP AND CREATE TO:

CREATE TO

Resultado:
 
DROP AND CREATE TO

Resultado:



Quando eu peço apenas o DROP TO funciona normalmente:

Resultado:


O ALTER TO também funciona, contudo ele não lista todas procedures da cadeia, o que também me parece ser um problema:



Fiz a reprodução do problema em um SQL Server 2005, 2008, 2008 R2,2012,2014 e tive o mesmo problema. Para obter uma reposta da Microsoft sobre este problema abri um caso no Connect e estou no aguardo da resposta, o link do caso está abaixo:


É isso pessoal, até a próxima, um abraço!