Re: Convert string to Number

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 03/13/04 

Date: Sat, 13 Mar 2004 20:20:45 +0530

hi ricky,

in addition to steve's post here is a procedural approach that will make use
of dynamic sql.

--sample table structure
create table formula(idd int identity(1,1),c1 varchar(40))
go
--sample data
insert into formula
select '150000*0.9*21/31 +100' union all
select '150000*0.8*21/28*(1+8/31)-100' union all
select '150000*0.87*21/31-100+199'
go
--stored procedure
create procedure exec_formulae
as
begin
set nocount on
declare @x int, @str nvarchar(150), @c1 varchar(50)
declare @t table (idd int, c1 varchar(40), formulae_value decimal(10,2))
declare @y decimal(10,2)

set @x = 0
while @x is not null
begin
select @x=min(idd) from formula
where idd > @x

select @c1 = c1 from formula where idd = @x

set @str = 'select @y=' + @c1

exec sp_executesql @str, N'@y decimal(10,2) output' , @y output
if @x is not null
insert into @t values(@x, @c1, @y)

end

select * from @t
end
go
--executing above stored procedure

exec exec_formulae 

--
Vishal Parkar
vgparkar@yahoo.co.in

Relevant Pages

  • Re: Dynamic SQL
    ... DECLARE @i_count int ... > I'm having problem getting the value from a query using dynamic SQL. ... Incorrect syntax near the keyword 'exec' ... > database or another database on another server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Variable Column Name in SELECT
    ... Dynamic sql with output parameter ... exec sp_executesql @dynamicsql, N'@TotalRecords int output', @TotalRecords ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem using SP_TRACE_SETFILTER - it doesnt seem to apply th
    ... @bigintfilter and @intfilter input parameters) because of the nature of the ... @duration int, -- in minutes ... 1.04 - customised to capture specific information for PrecisDM ... exec master.dbo.xp_cmdshell @RenameCmd,NO_OUTPUT ...
    (microsoft.public.sqlserver.server)
  • Re: Exec statements in stored procedures
    ... I'm only using dynamic sql because of the comma seperated list. ... >> second one is just a standard exec. ... >> CourseInstance table. ... >> only give rights to stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Disable Replication, remove rowguide-column?
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.replication)