x

execute dynamic sql from within a Function

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
more ▼

asked Aug 02, 2011 at 04:37 AM in Default

avatar image

Ahmed Mamdouh
13 1 1 3

is there any particular reason that it has to be within a function?

Aug 02, 2011 at 04:46 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Aug 02, 2011 at 06:27 AM

avatar image

Oleg
17.1k 3 7 28

I'll try this now and get back... thanks a lot

Aug 02, 2011 at 06:31 AM Ahmed Mamdouh

this is it, works like a charm :D:D

thanks man for this beautiful solution

Aug 02, 2011 at 06:53 AM Ahmed Mamdouh

Also solution @oleg and for performance better than using the scalar function. Only depending on the number of records returned I would use a normal temp table instead of table variable and Query optimizer takes a table variable like it contains 1 records. It doesn't use statistics for table variables. So then it can chose wrong join operator in the query plan.

Aug 02, 2011 at 08:03 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 02, 2011 at 06:00 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Excellent catch!! +1

Aug 02, 2011 at 06:02 AM WilliamD

very nice approach, but unforunately unapplicable in my deployment scenario as the deployment team distributes the application in different sites with different configuration for each site..

I may implement this solution if all other solutions failed..

Big Thanks anyway :D

Aug 02, 2011 at 06:08 AM Ahmed Mamdouh
(comments are locked)
10|1200 characters needed characters left

As @Kev Riley states, why a function??

If you place the code inside a stored procedure it will do what you want. As the error states, functions cannot execute dynamic sql.

more ▼

answered Aug 02, 2011 at 05:15 AM

avatar image

WilliamD
26.2k 18 34 48

Greetings,

Unfortunately I can't do that as I want to get a reult for each row of a table.. this is why I created a function to be used like this:

select fn_GetCurrencyDecimalDigits(CurrencyISOCode) from Products

Aug 02, 2011 at 05:35 AM Ahmed Mamdouh

I can't understand why you would want to have the schema split this way, so that you have to do a meta-data look-up before the actual function call happened. Wouldn't it make more sense to keep the function inside the same database as the Products table?

Just as a side note, calling a function the way you do is also going to have hidden performance problems by calling the function inline. Take a look at the CROSS APPLY and OUTER APPLY commands.

Aug 02, 2011 at 05:42 AM WilliamD

You might even be better off having multiple calls from the application layer, rather than the database having to work out where the config database is

Aug 02, 2011 at 05:44 AM Kev Riley ♦♦

Yes I agree with you, but this is the situation the currency decimal digits table is in a separate database other than the product and I'm working in a massive database .net product so we have to keep things that way :S, even if we had to sacrifice perfomance a little

Aug 02, 2011 at 05:47 AM Ahmed Mamdouh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x68
x57

asked: Aug 02, 2011 at 04:37 AM

Seen: 16623 times

Last Updated: Aug 02, 2011 at 04:55 AM

Copyright 2016 Redgate Software. Privacy Policy