I have multiple stored procedures, some that depend upon others. When publishing my database, I receive an error when creating some stored procedures due to dependency issues. SSDT seems to be creating the stored procedures in alphabetical order, not dependency order like I need.
For example, suppose sp_ProcA depends upon sp_ProcB. SSDT is trying to create sp_ProcA first and fails because sp_ProcB hasn't been created yet.
I've looked through project and script settings. I've found nothing related to dependency management.
Best Answer
The order in which Stored Procedures are created in SQL Server should not, in most cases**, cause any errors. If dependent objects do not exist at the time of
CREATE PROCEDUREorALTER PROCEDURE, you should merely get a warning about SQL Server not being able to register the dependency in the meta-data (i.e. sys.sql_expression_dependencies).If you are getting an actual error, then most likely you have a certain build option set to do this. Go to Project Properties and then to the Build tab. There is a check-box for "Treat Transact-SQL warnings as errors". You probably have that checked. You can either un-check that option OR if you like having it to catch other warnings, then trying specifying the warning in the "Suppress Transact-SQL warnings:" text field.
** Linked Servers / OPENQUERY / OPENROWSET, by default, attempt to validate the remote schema unless you alter the Linked Server definition to specify
LazySchemaValidation.