Wednesday, August 22, 2007

AppSettings in sql database

Solving Cross db Query db Name Issue
Maintain the db names in a custom Appsettings table

Introduction

sometimes you need to attach your databases as db_test or something which is other than original db name.

Workaround in vb days
Picking db Name from Ini

Workaround in .net days
Picking db Name from config files

But now we are also taking advantage of database programetibilty and we have queries like this
dbname.dbo.tableName


Workaround

1> Add a Setting Table in each database

Create Table dbSettings
(
key Nvarchar(10) Primary Key,
Value Nvarchar(30)

)

2> Insert the values

Insert Into dbSettings values('Brochure','Brochure_TT')
Insert Into dbSettings values('FIt','Fit_TT')
Insert Into dbSettings values('MIS','MIS_TT')
Insert Into dbSettings values('Invoice','Invoice_TT')
Insert Into dbSettings values('Presets','Presets_TT')
Insert Into dbSettings values('mydb','mydb_TT')


3> Add Function to get thease values

CREATE FUNCTION dbo.getdbSettings

(

@Key as Nvarchar(10)

)

RETURNS Nvarchar(30)

AS

BEGIN

Declare @Result as Nvarchar(30)
Select @Result = Value From dbSettings Where Key = @db
Return @Result

END



Now While Writing the Stored procedure


Replace
mydb.dbo.TableName

With
"+ dbo.getdbSettings('mydb') +".dbo.TableName

It works fine


Please suggest any other workarounds

Regards
Darshan Marathe