[dba-SQLServer] Reference table in different db

Mark,

I am probably the king of dynamic SQL.  I have dozens of stored procedures where I dynamically build 
  SQL to do everything from build tables with varying field names to populating said table, to 
building indexes, to BCP in / out.

In fact it is one of those applications where I am hitting the wall.  My client asks for a set of 
records WHERE (some long list of field compares).  That long list of field compares changes from 
order to order.  So I build a table (on-the-fly) where there is a base part (which never changes) 
and a dynamic part (where the select fields change for each order).

The way that I figured out to do this was to build a little table that will hold the names of the 
fields in my select query (the changing part).  I grab the field list and pop it into this little 
table.  I then open that field list and bring it into a cursor.  Using the cursor I compare each 
field name with the list of fields in the base table (some will be the same) and any field not in 
the base table gets added to the table using a modify table.

When I am done iterating the cursor, my base table now has each and every field in the selection 
query and is ready to accept the data coming from the selection query.

I then have to do the same kind of process again to build up an INSERT INTO query which takes the 
data coming from the query and insert into the order table.

So I use a cursor in these two specific places.  The cursors are small (1 to 10 field names) and are 
used to build dynamic SQL to build a custom table and then insert data into that custom table.

I THINK I can get around the whole "cursor in another database" issue by selecting the data into a 
tableset in memory using the WITH syntax.  I just haven't gotten around to trying that yet.

The bigger picture is that I have built up these stored procedures and they are contained inside of 
a template database.  I copy the template to an order database, then execute the SPs from there.  I 
am trying to move all of those SPs in the template / order db out to a master database that holds 
all of my SPs.  Mostly I have succeeded, but I have a handful of obstinate issues where that 
SPECIFIC sp has to remain in the template / order database for one reason or another, always 
revolving around having to execute inside of the db being modified.

I'm getting there though.  For someone who is not a SQL Server DBA, I am doing all right.

John W. Colby
www.ColbyConsulting.com


Mark Breen wrote:
> Hello John,
> 
> I can only answer half this query.