title: Linked Server Query / Dynamic SQL
body: <p>I currently have a linked server that I am querying in a stored procedure. I have the query working just fine currently however this query will need to change for every branch of code I have. I would like to know what the best method is for derriving the database name I am calling in the cross server query. </p>
  
  <p>Ex: 
  Server A has a link to server B. Server A contains 3 databases. SRV_A.DB1_DEV, SRV_A.DB2_Trunk, SRV_A.DB3_Prod Each are linked to their Server B counterpart... SRV_B.DB1_DEV, SRV_B.DB2_Trunk, SRV_B.DB3_Prod</p>
  
  <p>Each database on Server A has the same stored procedure. The only thing that changes in the sproc is the cross server select. So SRV_A.DB1_Dev has a select in the sproc that reads: </p>
  
  <pre><code>SELECT foo FROM [SRV_B].[DB1_DEV].[foo_table] WHERE bar = 1 
  </code></pre>
  
  <p>while the stored procedure on the trunk branch would be </p>
  
  <pre><code>SELECT foo FROM [SRV_B].[DB2_Trunk].[foo_table] WHERE bar = 1
  </code></pre>
  
  <p>Since I would like to have a VS project that will deploy the DB to every branch mentioned I would like to be able to fill in the database name dynamically. The solution I have came up with that is working is to use a series of IF checks with the CHARINDEX function, and then create the query with dynamic SQL, like this: </p>
  
  <pre><code>DECLARE @dSql NVARCHAR(4000);
  DECLARE @databaseName NVARCHAR(100) = DB_NAME();
  DECLARE @tableName NVARCHAR(100);
  IF SELECT CHARINDEX('Dev', @databaseName, 0)
      SET @tableName = '[SRV_B].[DB1_DEV].[foo_table]
      ...Same if &amp; set for Trunk
      ...Same if &amp; set for Prod
  SET @dSql = 'DECLARE @retID INT;SELECT foo FROM ' + @tableName 
  + ' WHERE bar = 1';SET @retID = SELECT SCOPE_IDENTITY()'
  EXEC(@dSQL);
  </code></pre>
  
  <p>I would have to imagine there is a better solution though, if anyone can help me with one, it would be much appreciated. If by some outside shot this is the best way let me know as well. </p>
  
  <p>Thanks,
  James</p>