Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: 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
    Code:
    (Width * Flange) + (((Height - Flange) * Leg) * Count)
    to
    Code:
    (108 * 4) + (((36 - 4) * 5) *2)
    Now I need to calculate the expression above, but the
    expression is a varchar string.

    Any help?

    Code:
    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 B

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    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.

    Code:
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, that was a cool solution. Dynamic SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •