Does anyone pull SQL files into their

queryExecute

functions? We have some stored procedure that I'd like to maintain some version control on, and it's much cleaner to do so in a separate SQL file but I'm not really sure if it's a good idea. I'm guessing the

FileRead

would be a good candidate for pulling the file. Does this make sense to do?

I'm interested in the area of version control for stored procedures. Do you currently have sql files for all your stored procedures? and how do you maintain them? I'm so used to just editing the stored procedures directly in the database and I have no version control. Don't know even know if thats possible within MSSQL Studio.

When we migrated to postgres 6 months ago we just started keeping one file for each procedure that we hosted in BitBucket. We would just make updates as necessary then use psql to replace the procedure. Once we switched to

cfmigrations

things got a little weird, we've just been creating copy/paste migration files based on the most recent procedure update, which certainly isn't ideal, hence why I'm wondering if this is possible.

Well its a great question. As I'm just starting to use cfmigrations and successfully ran

migrate up

for the first time today.

@Jason Ryan I think yer thinking about the wrong approach here. Leave yer procs in yer DB, but have the source code for them outside yer DB, just as .sql files (or whatever is appropriate for PostgreSQL). When you do a code release that needs to update the proc... run the .sql file as part of the release process. When you need to re-build yer DB (say for yer dev instance that'll need rebuilding fairly frequently), use those scripts in yer DB build process. I wouldn't normally recommend anything written by Jeff Atwood, but the list of 1-5 links at the bottom of this are useful: https://blog.codinghorror.com/get-your-database-under-version-control/

Not that this is an exmplar of best practices, but for any kind of db changes (modify schema or lookup table records), I create a .sql file that goes into source control. To apply those changes, I just copy the file content into a query editor window in ssms and run it on whichever dbs need it.

It's a good start.

We don't do scripted deploys, so that's pretty much the deal.

I have a folder in my source tree called 'database' and all the .sql files are placed there. They are a part of the 'git' tree that is placed in the repository. Everything is in that directory, all the db modifications, stored procedures, updated to indexes, foreign keys (and back out sections) in case I decided to dump the database (i.e. MySql, MS SQL or Oracle) and then restore some place else. This just makes life so much easier.

We've been using redgate source control for almost a decade, but I've been looking at dacpac deployments and flyway as alternatives recently. But the general pattern is the same: something scripts out all the objects into Sql files, and that's stored in git. And then something else "builds" them into a virtual database that can then be compared to a real database and make change scripts.