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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-29 : 09:28:03
|
| Don writes "How do you evaluate math functions and then put them into a variable? I have figured how to evaluate this formula but I am having trouble puting the results into a variable.Don Keeling--Evaluate formulaDeclare @Formula as varchar(150)Declare @Salary as varChar(50)Declare @Cost as moneyselect @Formula = '.171 *(3*vamount)/ 1000'Select @Salary = 30000 --Salary from Subscriber where Subscriberid = 112select @Formula = replace(@Formula,'vamount',@Salary) select @Formulaexec ('select' + @Formula)" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 10:01:41
|
| Don't use select - use SET.SET @variable = valueSET @variable = ( {select statement w/ one column} )setBasedIsTheTruepath<O> |
 |
|
|
dkeeling
Starting Member
2 Posts |
Posted - 2002-05-29 : 10:46:14
|
| you can not do this:SET @variable = exec ('select' + @Formula)This is what needs to be done otherwise it will not evaluate the formula. Maybe I misunderstood your solution setbasedisthetruepath. Please cut and paste my code into Query analyzer and add your solution then repost.ThanksDon Keeling |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 11:11:04
|
| I didn't quite follow your original post, I apologize.One rule with dynamic SQL ( your exec() call ) is that it executes in its own scope. You could put the results of the select into a variable, but it would do you no good b/c the variable would not be visible to the calling procedure.The only exception to that is temporary tables. A temp table created in a calling scope is visible in a called scope. You could conceivably run a 'create table #temp' in the calling, and then run a 'insert #temp select ...' in the exec(). But this is a poor, hacked approach.My advice is find a workaround for having to use dynamic SQL in the first place.setBasedIsTheTruepath<O> |
|
|
|
dkeeling
Starting Member
2 Posts |
Posted - 2002-05-29 : 11:39:31
|
| If there is another way of evaluating a function I would gladly change my angle. I have to store these functions with variables that are in other lookup tables, Im sure this has been done before. I tried using a #TMP table but being that the exec is in another scope it does not work. So I am using a real table that I create and destroy in this stored procedure. I would rather find a better solution, but if I cant I will just use this hack.Thanks,Don Keeling |
|
|
|
|
|
|
Evaluating Math Functions