title: SQL Function with Variable as db name within SELECT
body: <p>My first problem was trying to use a return value as a variable for the database name on a JOIN.</p>
  <p>I originally came across this website:
  <a href="https://dba.stackexchange.com/questions/86143/how-to-join-a-table-with-a-table-valued-function">Link</a></p>
  <p>From this I needed to create my first SQL function, and I've only been able to get somewhere with a Scalar-valued Function as I've been led to believe I need to declare variable and use the EXEC() to execute the query.</p>
  <p>My function looks like this:</p>
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE FUNCTION getDocumentNumber(@jobNo char(8), @docID int) 
  RETURNS varchar
  AS
  BEGIN
      DECLARE @dbName varchar(50)
      DECLARE @sql varchar(500)
      DECLARE @output varchar(50)
  
      SET @dbName = 'Job_' + @jobNo
      SET @sql = 'SELECT d.[Doc No Int] 
      FROM [' + @dbName + '].[dbo].[Documents] d 
      WHERE d.[id] = ' + @docID
  
      EXEC @sql = @output 
      RETURN @output
  END
  GO
  </code></pre>
  <p>My original SELECT statement now looks like this:</p>
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>SELECT CONCAT(ty.name, '-', co.code, ' ', FORMAT(rt.number, '000'), ' response overdue.') AS 'Description', 
      rt.[response_due_date] AS 'Date Due', 
      [dbo].getDocumentNumber(p.[Job Number], rt.[document_id]) AS 'Document No.', 
      p.[Job Number] AS 'Job No.', 
      p.[Job Title] AS 'Job Title', 
      p.[Project Manager], 
      'RFI / TQ' AS 'Type'  
  FROM [Project].[dbo].[RFI_TQ] rt
  JOIN [Project].[dbo].[RFI_TQ_Type] ty 
      ON rt.type_id = ty.id 
  JOIN [MyCompany].[dbo].[Company] co 
      ON rt.company_id = co.id 
  JOIN [Job_Register].[dbo].[Projects] p 
      ON rt.project_id = p.id 
  WHERE rt.[response_due_date] &lt; CURRENT_TIMESTAMP 
      AND p.[Project Manager] = 'Full Name' 
  ORDER BY rt.response_due_date DESC;</code></pre>
  </div>
  </div>
  </p>
  <p>When I try to execute I get the following error:</p>
  <blockquote>
  <p>Msg 245, Level 16, State 1, Line 1
  Conversion failed when converting the varchar value 'SELECT d.[Doc No Int] FROM [Job_00000001].[dbo].[Documents] d WHERE d.[id] = ' to data type int.</p>
  </blockquote>
  <p>I've looked and searched, I think my problem is getting the returned value from the EXEC() but I can't figure out how to write the SELECT statement with variable for database name without making the SELECT statement a varchar variable.</p>
  <p>Any help would be greatly appreciated.</p>
  <p>Thank you.</p>
  <p><em>Edit: apologies for the html code snippet, it would not let me post that section with Ctrl + K code indent.</em></p>