CODEWITHSUNDEEP
sql-server-2005
StuffHappens
StuffHappens

Reputation: 6557

create function sql server 2005 disable errors


I'm trying to write a *.bat file which runs all sql-scripts in given folder (every file in this folder has a create function script):

for /r "%~dp0\Production\Functions" %%X in (*.sql) do ( 
   sqlcmd -S%1 -d%2 -b -i "%%X"
)

But some functions in the folder are depended on others. So I get Invalid object name error. Is there a way to disable this error?

Upvotes: 0

Views: 223

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40289

It is not possible to disable errors generated by SQL when you run (what I think of as) code-based object: stored procedures, functions, views, triggers, and anything else that has to be the sole object of a batch submitted to SQL.

It is also awkward at best to work around this problem. Some options:

  • One way, as Joe Stefanelli recommends, is to name your files such that they get executed in proper order (by name, or perhaps by date created or something more esoteric).
  • Another way is to group related functions in single scripts, such that referenced objects must be created before referencing objects.
  • Or combine the above two, putting all your dependent objects in one script you can guarantee will always run first. Not so useful if your have nested references.
  • A last (and more kludgy) way is to iterate over your scripts several times (assuming your "create" script will properly deal with an object that already exists), until a given pass raises no errors.

For development purposes, we store code-based objects in individual files, but when it comes time to wrap the code up for push to Production systems, I glom the files together, test it, and shuffle the contents around and retest until no more errors are generated.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

Rename your files so that they're listed in the correct order of precedence. So, for example, if FuncA.sql uses FuncB.sql, then rename the files as 001-FuncB.sql, 002-FuncA.sql.

Upvotes: 1

Related Questions