I need help with the following (SQL Team Cross Post)

I am trying to setup a shape, shape attributes and calculate the cross
sectional area using the formula specified in the tbShapes.Formula field.
See the code below.

What this does is convert the formula

(Width * Flange) + (((Height - Flange) * Leg) * Count)

to

(108 * 4) + (((36 - 4) * 5) *2)

Now I need to calculate the expression above, but the
expression is a varchar string.

Any help?

USE NORTHWIND
GO

SET NOCOUNT ON
CREATE TABLE [dbo].[tbProductCodes] (
[ProductCode] [int] NOT NULL ,
[fkAccountID] [int] NOT NULL ,
[Product] [varchar] (50) NOT NULL ,
[fkShapeID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductCodes (ProductCode, fkAccountID, Product, fkShapeID)
SELECT 2001, 1, 'New Product', 1
GO

CREATE TABLE [dbo].[tbProductTemplateAttributeValues] (
[fkTemplateID] [int] NOT NULL ,
[fkAttributeID] [int] NOT NULL ,
[AttributeValue] [float] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue)
SELECT 1, 1, 108 UNION ALL
SELECT 1, 2, 36 UNION ALL
SELECT 1, 3, 4 UNION ALL
SELECT 1, 4, 5 UNION ALL
SELECT 1, 5, 2
GO

CREATE TABLE [dbo].[tbProductTemplates] (
[TemplateID] [int] NOT NULL ,
[fkProductCode] [int] NOT NULL ,
[Template] [varchar] (50) NOT NULL ,
[fkMixID] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductTemplates (TemplateID, fkProductCode, Template, fkMixID)
SELECT 1, 2001, 'ProductTemplate', 1
GO

CREATE TABLE [dbo].[tbShapeAttributes] (
[AttributeID] [int] NOT NULL ,
[fkShapeID] [int] NOT NULL ,
[Attribute] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

INSERT tbShapeAttributes (AttributeID, fkShapeID, Attribute)
SELECT 1, 1, 'Width' UNION ALL
SELECT 2, 1, 'Height' UNION ALL
SELECT 3, 1, 'Flange' UNION ALL
SELECT 4, 1, 'Leg' UNION ALL
SELECT 5, 1, 'Count'
GO

CREATE TABLE [dbo].[tbShapes] (
[ShapeID] [int] NOT NULL ,
[Shape] [varchar] (50) NOT NULL ,
[Formula] [varchar] (100) NULL
) ON [PRIMARY]
GO

INSERT INTO tbShapes (ShapeID, Shape, Formula)
SELECT 1, 'Double T', '(Width * Flange) + (((Height - Flange) * Leg) * Count)'
GO

CREATE PROCEDURE usp_shapes_GetCrossSection

@.iTemplate int,
@.cResult varchar (500) OUTPUT

AS

declare @.cAttribute varchar(50),
@.fAttribute float

-- Get the formula for the templates shape
SELECT @.cResult = s.Formula
FROM tbShapes AS s INNER JOIN tbProductCodes AS pc
ON s.ShapeID = pc.fkShapeID
INNER JOIN tbProductTemplates AS pt
ON pc.ProductCode = pt.fkProductCode
WHERE pt.TemplateID = @.iTemplate

SELECT @.cResult AS Formula

DECLARE AttributeCursor CURSOR FOR
SELECT sa.Attribute,
av.AttributeValue
FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa
ON av.fkAttributeID = sa.AttributeID
WHERE av.fkTemplateID = @.iTemplate

OPEN AttributeCursor
FETCH NEXT FROM AttributeCursor INTO @.cAttribute, @.fAttribute
while(@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cResult = REPLACE(@.cResult, @.cAttribute, CAST(@.fAttribute AS VarChar))
FETCH NEXT FROM AttributeCursor INTO @.cAttribute, @.fAttribute
END

SELECT @.cResult AS NewFormula

CLOSE AttributeCursor
DEALLOCATE AttributeCursor
GO

-- Test stored proc

declare @.iTemplate int, @.fResult float

SET @.iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @.iTemplate, @.fResult OUTPUT
SELECT @.fResult AS Result
GO

drop table [dbo].[tbProductCodes]
GO

drop table [dbo].[tbProductTemplateAttributeValues]
GO

drop table [dbo].[tbProductTemplates]
GO

drop table [dbo].[tbShapeAttributes]
GO

drop table [dbo].[tbShapes]
GO

DROP PROCEDURE usp_shapes_GetCrossSection
GO

Mike BMike, I guess I still don't get why your application requires this. Broken down, what you are doing is taking values stored as integers, running them through a procedure that casts them as characters buried in a string, and then looking for a procedure that strips them back out again?

It sound kind of circular.

Without too much difficulty, you could write a store procedure specific to the formula you gave that will parse the values out based on their positional relationship to the parenthesis characters, but it would not be a general solution. It would not work for any other formula.

If you can limit your operations to add, subtract, multiply, and divide, and if you can ensure that your formula will contain plenty of parenthesis to specify operation precedence, then without you might be able to write a recursive function that would be a general solution for simple formulas.|||Originally posted by blindman
Mike, I guess I still don't get why your application requires this. Broken down, what you are doing is taking values stored as integers, running them through a procedure that casts them as characters buried in a string, and then looking for a procedure that strips them back out again?

It sound kind of circular.

Without too much difficulty, you could write a store procedure specific to the formula you gave that will parse the values out based on their positional relationship to the parenthesis characters, but it would not be a general solution. It would not work for any other formula.

If you can limit your operations to add, subtract, multiply, and divide, and if you can ensure that your formula will contain plenty of parenthesis to specify operation precedence, then without you might be able to write a recursive function that would be a general solution for simple formulas.

Thanks for your reply BlindMan, but a couple of people at SQL Team found a solution.

declare @.iTemplate int, @.fResult varchar(500)

SET @.iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @.iTemplate, @.fResult OUTPUT

DECLARE @.stmt nvarchar(4000)
DECLARE @.param nvarchar(4000)
DECLARE @.Eval int

SET @.stmt='SET @.StmResult = ' + @.fResult
SET @.Param='@.StmResult int out'

EXEC sp_executesql @.stmt, @.Param, @.Eval OUT

SELECT @.Eval

Using the dynamic SQL the Equation can be computed. Anyway, if you would like to see the post it is at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34179

Mike B|||OK, that was a cool solution. Dynamic SQL.