|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Exec in TSQL (SQL Server 2000)Hi,
I need to know the result of a formula which contains placeholders for actual values. It works with writing the value to a table and read it again from there into a variable. But it must be an easier way to get the value to the variable. Or? TIA, Norbert Meiss DECLARE @strBOM_Formula AS VARCHAR(500) DECLARE @decPOM_Count AS Decimal (10,4) SELECT @strBOM_Formula = BOM_Formula FROM vBOM WHERE BOM_Key = 1 --value of @strBOM_Formula is now: ( 2 * B + 2 * H ) SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') Create Table #temp (CNT_Value Float Null) Exec ('Insert Into #temp (CNT_Value) Values (' + @strBOM_Formula + ')') Select @decPOM_Count = CNT_Value From #temp Drop Table tblCount why don't you use sp_Executesql and use an output parameter?
That won't work for what he's doing... he wants SQL Server to actually
parse his math expression for him, and passing it in as a parameter will not parse it - it will treat it as a literal string. Show quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:BE22E948-6FC2-4533-A824-BFBC30BBFC68@microsoft.com... > why don't you use sp_Executesql and use an output parameter? > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > something like this was what I was suggesting... using sp_Executesql
DECLARE @strBOM_Formula AS nVARCHAR(500) DECLARE @decPOM_Count AS Decimal (10,4) SELECT @strBOM_Formula = '(2 * B + 2 * H)' SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') declare @x int declare @query nvarchar(100) set @query = 'SELECT @x = ' + @strBOM_Formula execute sp_executesql @query, N'@x int output', @x output select @x My bad then, I thought you were suggesting passing @strBOM_Formula in as a
parameter. Show quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:6E24CC0E-FDA7-4B4C-AB31-AA9708223589@microsoft.com... > something like this was what I was suggesting... using sp_Executesql > > DECLARE @strBOM_Formula AS nVARCHAR(500) > DECLARE @decPOM_Count AS Decimal (10,4) > SELECT @strBOM_Formula = '(2 * B + 2 * H)' > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') > SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') > declare @x int > > declare @query nvarchar(100) > set @query = 'SELECT @x = ' + @strBOM_Formula > execute sp_executesql > @query, > N'@x int output', > @x output > select @x > > > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > Don't you bother... I wasn't very sure if it will work till I tried and
executed it :) You can use INSERT INTO ... EXEC, but other than that it's pretty much what
you have there. You are using SQL Server's EXEC to evaluate your expressions for you, which means you're effecitvely storing executable code in a table. This is pretty dangerous, as I'm sure you're already aware. SQL Injection and all that... DECLARE @strBOM_Formula AS VARCHAR(500) DECLARE @decPOM_Count AS Decimal (10,4) SELECT @strBOM_Formula = '(2 * B + 2 * H)' --value of @strBOM_Formula is now: ( 2 * B + 2 * H ) SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') Create Table #temp (CNT_Value Float Null) INSERT INTO #temp (CNT_Value) EXEC ('SELECT ' + @strBOM_Formula ) SELECT * FROM #temp Drop Table #temp Show quote "Norbert Meiss" <NorbertMe***@discussions.microsoft.com> wrote in message news:6AC34590-51D0-459D-9F87-F54780DC588E@microsoft.com... > Hi, > I need to know the result of a formula which contains placeholders for > actual values. It works with writing the value to a table and read it > again > from there into a variable. But it must be an easier way to get the value > to > the variable. Or? > TIA, Norbert Meiss > > DECLARE @strBOM_Formula AS VARCHAR(500) > DECLARE @decPOM_Count AS Decimal (10,4) > SELECT @strBOM_Formula = BOM_Formula FROM vBOM WHERE BOM_Key = 1 > --value of @strBOM_Formula is now: ( 2 * B + 2 * H ) > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') > SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') > Create Table #temp (CNT_Value Float Null) > Exec ('Insert Into #temp (CNT_Value) Values (' + @strBOM_Formula + ')') > Select @decPOM_Count = CNT_Value From #temp > Drop Table tblCount > Thanks for your hints - I didn't consider the implications so far.
Show quote "Mike C#" wrote: > You can use INSERT INTO ... EXEC, but other than that it's pretty much what > you have there. You are using SQL Server's EXEC to evaluate your > expressions for you, which means you're effecitvely storing executable code > in a table. This is pretty dangerous, as I'm sure you're already aware. > SQL Injection and all that... > > DECLARE @strBOM_Formula AS VARCHAR(500) > DECLARE @decPOM_Count AS Decimal (10,4) > SELECT @strBOM_Formula = '(2 * B + 2 * H)' > --value of @strBOM_Formula is now: ( 2 * B + 2 * H ) > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') > SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') > Create Table #temp (CNT_Value Float Null) > INSERT INTO #temp (CNT_Value) > EXEC ('SELECT ' + @strBOM_Formula ) > SELECT * FROM #temp > Drop Table #temp > > "Norbert Meiss" <NorbertMe***@discussions.microsoft.com> wrote in message > news:6AC34590-51D0-459D-9F87-F54780DC588E@microsoft.com... > > Hi, > > I need to know the result of a formula which contains placeholders for > > actual values. It works with writing the value to a table and read it > > again > > from there into a variable. But it must be an easier way to get the value > > to > > the variable. Or? > > TIA, Norbert Meiss > > > > DECLARE @strBOM_Formula AS VARCHAR(500) > > DECLARE @decPOM_Count AS Decimal (10,4) > > SELECT @strBOM_Formula = BOM_Formula FROM vBOM WHERE BOM_Key = 1 > > --value of @strBOM_Formula is now: ( 2 * B + 2 * H ) > > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'H', '1273') > > SET @strBOM_Formula = REPLACE(@strBOM_Formula, 'B', '3456') > > SET @strBOM_Formula = REPLACE(@strBOM_Formula, ' ', '') > > Create Table #temp (CNT_Value Float Null) > > Exec ('Insert Into #temp (CNT_Value) Values (' + @strBOM_Formula + ')') > > Select @decPOM_Count = CNT_Value From #temp > > Drop Table tblCount > > > > >  |
|||||||||||||||||||||||
Other interesting topics