[Organizing and sharing skills 2] using the sp_oa series to store the course, processing the results of the data Formula

 

 

Use tempdb

Go

If object_id ('tab1') is not null

Drop table tab1

Go

Create Table tab1 (col1 real, formula varchar (50 ))

Insert into tab1 select 1000, '+ 20.5 × rows. 1'

Insert into tab1 select 50, 'sequence. 5 × + 123'

Insert into tab1 select-50, '+ 100 + (23.0 × )'

Go

 

If object_id ('roy _ OA ') is not null

Drop function roy_oa

Go

Create Function roy_oa (

@ STR varchar (1000) -- expression to be calculated

) Returns SQL _variant

As

Begin

Declare @ Re SQL _variant

 

Declare @ err int, @ SRC varchar (255), @ DESC varchar (255)

Declare @ OBJ int

 

Exec @ err = sp_oacreate 'msscriptcontrol. scriptcontrol', @ OBJ out

If @ err <> 0 goto lb_err

 

Exec @ err = sp_oasetproperty @ OBJ, 'language', 'vbscript'

If @ err <> 0 goto lb_err

 

Exec @ err = sp_oamethod @ OBJ, 'eval', @ Re out, @ Str

If @ err = 0 return (@ Re)

 

Lb_err:

Exec sp_oageterrorinfo null, @ SRC out, @ descout

Declare @ errb varbinary (4), @ s varchar (20)

Set @ errb = cast (@ err as varbinary (4 ))

 

Return (n' Error Source: '+ @ SRC + char (13) + N' error Description:' + @ DESC)

End

Go

 

 

Select

Col1,

Formula,

DBO. roy_oa (cast (col1 as varchar) + Replace (replace (formula, '×', '*'), 'hour', '/') as result

From tab1

 

/*

Col1 formula result

1000 + 20.5x1023.47328244275. 1

50 rows. 5 x + 23.0 2023

-50 + 100 + (23.0 ×) 188

*/