Cadavre (1/11/2012)
In this case, you'd have to use a CLR as you can't execute a string in a function. If you need help writing one (and someone else doesn't post one in between), I'll knock up a simple one later on today.
Well, running a little late obviously 😀
Nice and simple CLR, I've not exactly thoroughly tested this so you'll want to do lots of tests first.
using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace executeString
{
public class UserDefinedFunctions
{
[SqlFunction]
public static SqlString ExecuteString(SqlString maths)
{
var mathString = maths.ToString().Replace(" ", "");
string mathResult;
try
{
var result = new DataTable().Compute(mathString, null);
mathResult = result.ToString();
}
catch (Exception)
{
mathResult = null;
}
mathResult = string.IsNullOrEmpty(mathResult) ? null : mathResult;
return new SqlString(mathResult);
}
};
}
BEGIN TRAN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExecuteString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ExecuteString]
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'executeString' and is_user_defined = 1)
DROP ASSEMBLY [executeString]
GO
CREATE ASSEMBLY [executeString]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030054AE0E4F0000000000000000E00002210B010800000A00000006000000000000FE270000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000AC2700004F00000000400000A803000000000000000000000000000000000000006000000C000000082700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000000408000000200000000A000000020000000000000000000000000000200000602E72737263000000A80300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000E0270000000000004800000002000500CC2000003C06000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3003005600000001000011000F00FE16020000016F0F00000A720100007072050000706F1000000A0A00731100000A0614281200000A0C086F0F00000A0B00DE072600140B00DE000007281300000A2D03072B01140B07731400000A0D2B00092A00000110000000001E0018360007140000011E02281500000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000008020000237E000074020000D402000023537472696E6773000000004805000008000000235553005005000010000000234755494400000060050000DC00000023426C6F620000000000000002000001471502000900000000FA2533001600000100000014000000020000000200000001000000150000000C00000001000000010000000200000000000A0001000000000006004F0048000A00770062000600AD009B000600C4009B000600E1009B00060000019B00060019019B00060032019B0006004D019B00060068019B000600A00181010600B4019B000600E001CD013700F40100000600230203020600430203020A007C02610206009A0248000A00A90256000600C90248000000000001000000000001000100010010001C003100050001000100502000000000960081000A000100C4200000000086188F001100020000000100950019008F00150021008F00150029008F00150031008F00150039008F00150041008F00150049008F00150051008F00150059008F001A0061008F00150069008F001F0079008F00250081008F00110089008F001100090091022F009100A102330099008F0011009900B30239009100BB023F0011008F00150009008F001100200073002A002E002B0074002E0013005F002E001B005F002E00230065002E000B004C002E00330088002E003B005F002E004B005F002E005B00A9002E006300B2002E006B00BB00440004800000010000002A11EA450000000000003100000002000000000000000000000001003F000000000002000000000000000000000001005600000000000000003C4D6F64756C653E0065786563757465537472696E672E646C6C0055736572446566696E656446756E6374696F6E730065786563757465537472696E67006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670045786563757465537472696E67002E63746F72006D617468730053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E67005265706C61636500446174615461626C6500436F6D707574650049734E756C6C4F72456D70747900457863657074696F6E00000003200000010000070C0F76F281EE4585615E6253F934ED0008B77A5C561934E0890600011109110903200001042001010E0420010102052001011139042001010804010000000320000E0520020E0E0E0520021C0E0E040001020E0707040E0E1C11091201000D65786563757465537472696E6700000501000000000E0100094D6963726F736F667400001301000E736561726368466561747572657300002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000054AE0E4F000000000200000088000000242700002409000052534453E6566FF3F30E514ABEA3B66178A5B81203000000433A5C55736572735C6372616967775C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C73656172636846656174757265735C73656172636846656174757265735C6F626A5C44656275675C65786563757465537472696E672E70646200D42700000000000000000000EE270000002000000000000000000000000000000000000000000000E0270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C03000000000000000000004C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100EA452A1100000100EA452A113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC020000010053007400720069006E006700460069006C00650049006E0066006F00000088020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000044000E000100460069006C0065004400650073006300720069007000740069006F006E0000000000650078006500630075007400650053007400720069006E006700000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003300390034002E00310037003800390038000000000044001200010049006E007400650072006E0061006C004E0061006D0065000000650078006500630075007400650053007400720069006E0067002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000650078006500630075007400650053007400720069006E0067002E0064006C006C00000040000F000100500072006F0064007500630074004E0061006D00650000000000730065006100720063006800460065006100740075007200650073000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003300390034002E00310037003800390038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003300390034002E0031003700380039003800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [dbo].[ExecuteString](@maths [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [executeString].[executeString.UserDefinedFunctions].[ExecuteString]
GO
CREATE TABLE yourTable (yourTableID INT IDENTITY, column1 VARCHAR(500), column2 VARCHAR(500))
INSERT INTO yourTable (column1)
SELECT '2 * 2+1 * 7'
UNION ALL SELECT '1 * 1+2 * 4'
UNION ALL SELECT '1 * 1+2 * 4+1 * 4'
SELECT column1, [dbo].[ExecuteString] (column1)
FROM yourTable
ROLLBACK
Which will return: -
column1
------------------- --------------------
2 * 2+1 * 7 11
1 * 1+2 * 4 9
1 * 1+2 * 4+1 * 4 13
Since you never replied to my last message, I'm assuming that a CLR is an option for you?