Dynamic SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL problem

Author

Topic

marginerazvan
Starting Member

14 Posts

Posted - 2007-06-14 : 07:17:27

Hi there.

I have a table (FORMULA_DEFINITIONS) that contains
PROFILE int
FORMULA varchar(100)

The formula COLUMN contains different formulas for different profiles.
For example
1 5*3+12
2 10*5*4
etc

I have a function dbo.ReturnResultOfFormula that must return the result of the formula applied for a profile.
Ex. ReturnResultOfFormula(1) must return 27
ReturnResultOfFormula(2) must return 200

Here is the function definition:
CREATE FUNCTION dbo.ReturnResultOfFormula(@Profile int)
RETURNS int as
BEGIN

DECLARE @Result int, @SQL varchar(100)
SELECT @SQL = FORMULA FROM FORMULA_DEFINITIONS WHERE PROFILE = @Profile

SET @Result = @SQL
(it will not work because it will return '5*3+12')

SET @SQL = N'SELECT @Result = ' + @SQL
EXEC sp_executesql @SQL
(it will not work because I cannot use EXEC inside of user-defined-function)

RETURN @Result
GO

What should I do? How can I get the results of formula? How can I correctly use the Dynamic SQL

I will appreciate any suggestions/solutions.
Thank you for help.

PS. The real situation is more complicated, but I summarized it.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-14 : 08:34:14

can't do it in an in-line statement (without an extended sp or clr in v2005).
You would have to loop through and do it in an SP
or
declare @result int, @sql nvarchar(1000)
select @sql = 'select @result = ' + @formula

exec sp_executesql @sql, N'@result int out'), @result out

(Looks very much like something I came across to value financial instruments)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

marginerazvan
Starting Member

14 Posts

Posted - 2007-06-14 : 09:19:29

The error message says:
Only functions and extended stored procedures can be executed from within a function.

quote:
Originally posted by nr

declare @result int, @sql nvarchar(1000)
select @sql = 'select @result = ' + @formula

exec sp_executesql @sql, N'@result int out'), @result out

(Looks very much like something I came across to value financial instruments)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 09:24:02

You cant use Dynamic SQL in a Function. Make use of Stored procedure instead

Madhivanan

Failing to plan is Planning to fail

marginerazvan
Starting Member

14 Posts

Posted - 2007-06-14 : 10:20:28

The problem is that my function is used in SELECT query statement
Ex. SELECT dbo.ReturnResultOfFormula(MATERIAL.PROFILE), MATERIAL.MATERIAL_ID, etc
If I will transform it in a SP, I will not be able to use it in SELECT queries.

Or am I wrong?

quote:
Originally posted by madhivanan

You cant use Dynamic SQL in a Function. Make use of Stored procedure instead

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-15 : 03:33:16

One of methods I can think of is

create table #formula (formula varchar(1000))
insert into #formula  
select 'select '+cast(col as varchar(10))+' as col,'+value+' as value union all ' from yourtable
select * from #formula  

Declare @sql varchar(8000)
select @sql=''
Select @sql=@sql+ formula from #formula
Select @sql=left(@sql,len(@sql)-len(' union all '))
exec(@sql)

You can export the result to another temp table and match it with your original table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources