Follow @endyourif rss Facebook LinkedIn

Using DB_NAME with MSSQL Using DB_NAME with MSSQL

Published on Jan 31, 2020

I'll admit, I haven't used the DB_NAME very often. I pretty much use it in a single scenario. With all of my projects I use Continuous Deployment or Continuous Integration, whatever your choice of word is. When doing CI I have automatic deployment of any database scripts that need to run as I deploy to each environment: local, development, production, etc.

On occasion, a script needs to apply a custom value for each environment like a user ID. To accomplish I use DB_NAME to perform a case statement as follows.

Leveraging DB_NAME to apply custom logic

First I start by storing the name in a variable. Then I (typically) perform an update statement applying a case statement to specify the unique value per environment. You will need to know the database name of each environment:

DECLARE @dbName nvarchar(128) 

SELECT @dbName = DB_NAME()

	ValueField = 
		WHEN 'LocalDatabase' THEN 1
		WHEN 'DevelopmentDatabase' THEN 2
		WHEN 'ProductionDatabase' THEN 3

In the example above, the table, field, database names, and the value will need to be updated to match your requirements.


My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP