I want to calculate a string, so I have created this Function, but its
giving error any idea please ?
Best Regards,
Luqman
Create function eval
(@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
declare @.myString varchar(50)
select @.mystring=exec('select '+@.Mycalcstring)
return(@.myString)
endHi
I am not sure why you don't use cast/convert directly, although this would
depend on the quality of what you are passing to the function.
Create function eval (@.Mycalcstring varchar(50))
returns decimal(12,2)
as
begin
return(CAST(@.Mycalcstring AS decimal(12,2)))
end
SELECT dbo.eval('12.2')
John
"Luqman" wrote:
> I want to calculate a string, so I have created this Function, but its
> giving error any idea please ?
> Best Regards,
> Luqman
> Create function eval
> (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> declare @.myString varchar(50)
> select @.mystring=exec('select '+@.Mycalcstring)
> return(@.myString)
> end
>
>|||Say, I want to calculate this string : '(10-5)*7/2'
select cast('(10-5)*7/2' AS decimal(12,2))
will give error :
Error converting data type varchar to numeric.
But if I use:
Select (10-5)*7/2 ' It will return 17 which is Correct Result.
So, please advise whats wrong with my function.
Best Regards,
Luqman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:CD3C2647-4BAF-4499-A6B9-06D275405010@.microsoft.com...
> Hi
> I am not sure why you don't use cast/convert directly, although this would
> depend on the quality of what you are passing to the function.
> Create function eval (@.Mycalcstring varchar(50))
> returns decimal(12,2)
> as
> begin
> return(CAST(@.Mycalcstring AS decimal(12,2)))
> end
> SELECT dbo.eval('12.2')
> John
> "Luqman" wrote:
>|||Luqman wrote:
> Say, I want to calculate this string : '(10-5)*7/2'
> select cast('(10-5)*7/2' AS decimal(12,2))
> will give error :
> Error converting data type varchar to numeric.
> But if I use:
> Select (10-5)*7/2 ' It will return 17 which is Correct Result.
> So, please advise whats wrong with my function.
> Best Regards,
> Luqman
Dont use the ' because they indicate a character column.
SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||Such calculations are stored in a Table with Varchar Type, so I need to
calculate their values, and as such I need this function.
Following lines will work, but how can I put the select result in Variable.
declare @.myString as varchar(50)
declare @.myValue as Decimal(12,2)
set @.myString='(10-5)/2'
exec ('select ' + @.myvalue) 'This works
set @.myValue=exec ('select ' + @.myvalue) 'This does not work
Best Regards,
Luqman
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
> Luqman wrote:
> Dont use the ' because they indicate a character column.
> SELECT CAST(((10-5)*7/2) AS DECIMAL(12,2))|||declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue
hth,
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Such calculations are stored in a Table with Varchar Type, so I need to
> calculate their values, and as such I need this function.
> Following lines will work, but how can I put the select result in
Variable.
> declare @.myString as varchar(50)
> declare @.myValue as Decimal(12,2)
> set @.myString='(10-5)/2'
> exec ('select ' + @.myvalue) 'This works
> set @.myValue=exec ('select ' + @.myvalue) 'This does not work
> Best Regards,
> Luqman
>
>
> "Jo Segers" <jo.segers@.alro.be> wrote in message
> news:eo04ybPDFHA.512@.TK2MSFTNGP15.phx.gbl...
>|||avnrao wrote:
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
>
Or if you realy want your 2 decimals:
declare @.myString as nvarchar(50)
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = 1.0*' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
print @.myValue|||Hi,
I have created following function in master database and when select it with
:
Select dbo.eval('10-5')
Error occured:
Server: Msg 557, Level 16, State 2, Procedure eval, Line 9
Only functions and extended stored procedures can be executed from within a
function.
Any idea please?
create function eval
(@.myString as varchar(50))
returns decimal(12,2)
as
begin
declare @.myValue as decimal(15,2)
set @.myString='(10-5)/2'
set @.myString = 'select @.myValue = ' + @.myString
exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
return(@.myValue)
end
"avnrao" <avn@.newsgroups.com> wrote in message
news:u8v6gEQDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> declare @.myString as nvarchar(50)
> declare @.myValue as decimal(15,2)
> set @.myString='(10-5)/2'
> set @.myString = 'select @.myValue = ' + @.myString
> exec sp_executesql @.myString, N'@.myValue decimal(15,2) out',@.myValue out
> print @.myValue
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "Luqman" <pearlsoft@.cyber.net.pk> wrote in message
> news:uH2gmyPDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Variable.
>|||You cannot run dynamic SQL from within a function. EXEC and
sp_executesql are not allowed. Either use an SP or, better still IMO,
do it in some external code. I don't understand why you would want to
do this in a database.
Also, I wouldn't recommend putting functions or other objects in Master
on a production system.
David Portas
SQL Server MVP
--|||Hi,
I just want to create a function which should calculate a String, and return
the result, someone recommended this way ?
If you have better solution, please adv.
Best Regards,
Luqman
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107775757.602219.248630@.o13g2000cwo.googlegroups.com...
> You cannot run dynamic SQL from within a function. EXEC and
> sp_executesql are not allowed. Either use an SP or, better still IMO,
> do it in some external code. I don't understand why you would want to
> do this in a database.
> Also, I wouldn't recommend putting functions or other objects in Master
> on a production system.
> --
> David Portas
> SQL Server MVP
> --
>
