I'm creating a function that executes a dynamic sql to **fetch data from another database that is unknown to me and I'm getting the database name by a query**.. of course though the function is created I'm getting "only functions and extended stored procedures can be executed from within a function" error message when calling the function.. **is there any workaround?** ------------------------------------------------ Create Function fn_GetCurrencyDecimalDigits (@currency nvarchar (3)) returns int as begin Declare @Database nvarchar(50) set @Database = (select [Value] from configuration where [Key] = 'database') Declare @Sql nvarchar(max) Set @SqI = N'SELECT CurrDecimalDigit FROM ' + @Database + '..Currency WHERE CurrlSOCode = ''' + @currency + '''' Declare @res int exec sp_executesql @query = @Sql, @params = N’@res int output’, @res = @res output return @res end
Using a multiline scalar function is really evil and should be avoided if at all possible. In your case, it would be easy enough to query the Currency table in the specified database only once rather than torture it once for each row in the Products table. How about creating a proc first and then using it to join its results with Products table after whatever records the proc returns are stored in the table variable: create proc dbo.usp_GetCurrencyDecimalDigits as begin; set nocount on; declare @sql nvarchar(max); select @sql = N'select CurrDecimalDigit, CurrlSOCode from ' + [Value] + '..Currency' from configuration where [Key] = 'database'; exec (@sql); set nocount off; end; go Once the procedure is created, you can use it like this (in place of your **select fn\_GetCurrencyDecimalDigits(CurrencyISOCode) from Products** thingy): declare @t table (CurrDecimalDigit int, CurrlSOCode varchar(50)); insert into @t exec dbo.usp_GetCurrencyDecimalDigits; select t.CurrDecimalDigit --, Products.* from Products left join @t t on Products.CurrencyISOCode = t.CurrlSOCode; Oleg
You can workaround this by creating a static SQL in your function, but instead of using a real database name and table, you will create a Synonym for which will point to the right database and table and use that **table synonym** in your static query . In case the DB name in the future is changed, you only alter the synonym and you are ready.