Pass Database Name to a Function and setting the database to query

[M8KWR]

I need to pass 2 parameters to a function, one with be a guid I use to query a table to get the database I need to look in, then other will be another guid value I need to find the description of.

I am trying to do something like this:

Create Function [dbo].[get_Description] (@DB_GUID varchar(36), @Value_GUID varchar(36))
RETURNS VarChar(50)
AS
Begin
declare @db_path varchar(36)
declare @Value_Description varchar(36)

SELECT @db_path = DB_Path 'FROM Databases where db_guid = @DB_GUID

SELECT @Value_Description = description from @db_path.dbo.table_of_descriptions WHERE (Value_GUID = @Value_GUID )

return @Value_Description
End

It is obviously failing on the 2nd select where I am trying to pass the DB_path to set the database I require the statement to look at.

Any assistance would be great, and thanks in advance.

 

[bborissov]

try this:

Create Function [dbo].[get_Description] (@DB_GUID varchar(36), @Value_GUID varchar(36))
RETURNS VarChar(50)
AS
Begin
declare @db_path varchar(36)
declare @Value_Description varchar(36)
declare @SQL nvarchar(3000)

SELECT @db_path = DB_Path FROM Databases where db_guid = @DB_GUID

SET @SQL = 'SELECT @Value_Description = description from '+@db_path+'.dbo.table_of_descriptions WHERE (Value_GUID = @Value_GUID )'
EXEC sp_executesql @SQL, N'@Value_Description varchar(36) OUTPUT, @Value_GUID varchar(36)', @Value_Description = @Value_Description OUTPUT, @Value_GUID = @Value_GUID

return @Value_Description
End

Not sure is Dynamic SQL will work in Function.
BUT if you want to use this in a SELECT from a table with many records you are doomed
THAT IS SLOOOOOOW!




Borislav Borissov
VFP9 SP2, SQL Server

 

[M8KWR]

Thanks for this, but it will not execute, i am getting this error message

Only functions and some extended stored procedures can be executed from within a function.

 

[bborissov]

How about to use SP?
Then you can create a temp table fill it from every DB you have and use that TEMP in JOIN?
That way you don't need this SP"

CREATE TABLE #TmpDesc (DatBase varchar(200), Description varchar(200), Value_GUID varchar(36))

declare @db_path varchar(36)
declare @SQL nvarchar(3000)
SELECT @db_path = MIN(DB_Path)
        FROM Databases

WHILE @db_path IS NOT NULL
      BEGIN
           SET @SQL = 'SELECT '+@db_path+' AS DataBase,
                               description,
                               Value_GUID
                       from '+@db_path+'.dbo.table_of_descriptions'
          INSERT INTO #TmpDesc (DatBase, Description, Value_GUID)
          EXEC sp_executesql @SQL
         SET  @db_path = (SELECT MIN(DB_Path)
                                 FROM Databases
                          WHERE Db_Path >  @db_path)
      END

SELECT (main query field list), Tmp.Description
FROM YourMainTable
INNER JOIN  #TmpDesc Tmp ON .... AND DatBase = ????


DROP TABLE #TmpDesc

This is VERY rough example, because I don't know how your query is organized, from where you get @DB_GUID varchar(36), @Value_GUID. If they are fixed values then you can do a very simple query, but if you get these values from a table , then you should fill #TempTable


Borislav Borissov
VFP9 SP2, SQL Server

 

[M8KWR]

Many thanks for this, i will give it ago