Using Dynamic SQL in User Defined Function to retu

Our document storage application has a unique database for each of our clients which are almost identical to each other, but one table DocumentIndexes is unique for each client and can have any number of columns and types.

I am trying to create a generic function (within our "master" database called MYAPP_MASTER) that I can call and simply pass in a database name and a document ID value and get back the column names and values from from the specified database's DocumentIndexes table. Because I have to pass in the database name, I have to generate the selection SQL dynamically and call sp_executesql.

I have the following code which polls the INFORMATION_SCHEMA.COLUMNS table to determine the columns needed and it works just fine in a stored procedure, but I hate having to copy all this code in every stored procedure that needs these to retrieve these dynamic column values. I would rather have one function that returns the string value of these columns regardless of database and have the function exist once in our MYAPP_MASTER database. Again, this code works, but SQL won't allow me to put it into a function. Is there anyway around this?

USE MYAPP_MASTER
GO
DECLARE @DatabaseName varchar(255)
DECLARE @DocumentId int
SET @DatabaseName = 'SAMPLE_CLIENT_DB'
SET @DocumentId = 1234
DECLARE @DynamicIndexes nvarchar(max)
DECLARE @DynamicIndexesParam nvarchar(max)
DECLARE @DynamicIndexesSql nvarchar(max)
SET @DynamicIndexesParam = '@Indexes varchar(max) OUTPUT'
SET @DynamicIndexesSql = 'SELECT @Indexes = COALESCE(@Indexes + ''+ '''', '', '''') + CAST(COLUMN_NAME as varchar(max)) + '': '''''' + '' + CASE WHEN DI.'' + COLUMN_NAME + '' IS NOT NULL THEN CAST(DI.'' + COLUMN_NAME + '' as varchar(max)) ELSE '''''''' END '' FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''DocumentIndexes'' AND COLUMN_NAME <> ''DocumentID''; '
EXEC sp_executesql @DynamicIndexesSql, @DynamicIndexesParam, @Indexes = @DynamicIndexes OUTPUT
SET @DynamicIndexes = '''' + @DynamicIndexes
DECLARE @SelectionSql nvarchar(max)
SET @SelectionSql = 'SELECT ' + @DynamicIndexes + ' as DocumentIndexes FROM ' + @DatabaseName + '..Document D LEFT OUTER JOIN ' + @DatabaseName + '..DocumentIndexes DI ON D.DocumentId = DI.DocumentId WHERE D.DocumentID = ' + CAST(@DocumentId as varchar(10))
EXEC sp_executesql @SelectionSql

If the SAMPLE_CLIENT_DB datababase DocumentIndexes table has columns for Name, Office and Classification, this code will return a simple string that looks like the following:

Name: Foo, Office: Bar, Classification: 123

You can't run an Exec command inside a SQL function, but you could use a stored procedure with an output variable assuming the DocumentIndexes table is unique at the DocumentID level.

Create Proc DocID_DocIndexes @retval Varchar(Max) Output
As
...
(Your code logic minus last two lines)
...
--  Populate your dynamic SQL into a variable to assign it to the output variable
SET @SelectionSql = 'SELECT @result = ' + @DynamicIndexes + ' as DocumentIndexes FROM ' + @DatabaseName + '..Document D LEFT OUTER JOIN ' + @DatabaseName + '..DocumentIndexes DI ON D.DocumentId = DI.DocumentId WHERE D.DocumentID = ' + CAST(@DocumentId as varchar(10))
EXEC sp_executesql @SelectionSql, N'@result Varchar(Max) Output', @result = @retval Output
Return