T-sql – Any way to make stored procedure code dynamically reference other databases, without dynamic sql

dynamic-sqlstored-proceduressynonymst-sql

I've a situation where I have two databases:

ABCData
ABCUsers

One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:

INSERT ABCUsers.dbo.Table1 VALUES('a');

But the next problem we run into is that we want to backup and redeploy these database pairs to similarly named other databases for testing and dev purposes:

XYZData
XYZUsers

Which means the XYZData database will contain a sproc that doesn't work – because the insert has a hard coded ABCUsers.dbo.Table1, and we don't want to get into changing it

We could find out the current database name in the sproc, adjust it and run the sql dynamically:

DECLARE @DataDbName sysname = (DB_NAME());
DECLARE @UserDBName NVARCHAR(20) = (SELECT REPLACE(@DataDbName, 'Data', 'Users'));
EXEC CONCAT('INSERT ', @UserDbName, '.dbo.Table1 VALUES(''a'');')

But I think the prospect of doing so actually makes me feel physically ill 😉 (not least because this is a massively simplified example of tens of sprocs each making tens of calls to other dbs)

Is there a way to alias a database name during its creation, so the sproc can always say something like:

INSERT UserDbAlias.dbo.Table1 VALUES('a');

And when the ABCUsers database is created, the UserDbAlias -> ABCUsers but when XYZUsers db is created UserDbAlias -> XYZUsers

Alternatively, this is a git/tfs source control – is there a way to write the source control version of the sproc so that it is modified dynamically when it's deployed, and the original source can contain some kind of placeholder for the db names, but they're replaced with actual hard coded varying db names upon deploy (automatically)

Footnotes: I looked at A way to reference other DB without hardcoding its name which does seem to describe my problem, but doesn't have an answer. That question mentioned "I've looked at synonyms but they wouldn't work out with EF6.." (paraphrase)

Would synonyms work out for me? We use EF6, but I'm not sure what the actual problem is in terms of why it wouldn't work out. Our sproc name in the db never changes, and we don't need to alias the sproc itself, it's the insert table name we need to alias. If the sproc is always called Sproc1 regardless of whether it's in ABCData or XYZData, and the synonym resolving happens inside the sproc, would EF6 even have a problem with it?

Best Answer

Apart from design considerations raised in the comments, ...

Consider one level of indirection prior to creation of the stored procedure:

write a template for the stored procedure, with token placeholders for the schema names

INSERT DBNAMETOKEN.dbo.Table1 VALUES('a');

write a pre-deployment script (sed?) that creates a new file specifically for each schema, substituting the actual schema name for the token.
place resulting scripts in per-schema deployment directories, from which they may be installed as usual.

Benefits

Scales well
Both template and custom versions may be under version control
Updates/Fixes may still be deployed through a single change to the template file and propagated through a build step
Possibly easier to schedule/track deployment of updates to each schema depending on situation
Reduces the stored procedure to a simple(r) set of SQL focussed on the task at hand, rather than the dynamics of making it work across different schema.

Related Solutions

Sql-server – Stored Procedure to return dynamically created table data

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t 
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script

Sql-server – Reference the database from a stored proc

If db_id() is called with no parameter it returns the id of the current database - http://msdn.microsoft.com/en-us/library/ms186274.aspx

so you could just use: set @db_id=db_id()

And having read the rest of the question, you could call object_id() without the db name. If the object exists you'll get the id, if not NULL. So: SET @object_id = OBJECT_ID(N'dbo.Users');

Best Answer

Benefits

Related Solutions

Sql-server – Stored Procedure to return dynamically created table data

Sql-server – Reference the database from a stored proc

Related Question