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

USE NORTHWIND
GO

create proc test
@input varchar(500) output,
@var1 varchar(500) output

as

set @var1 = @input

return

GO

declare @int int, @Input varchar(500)

set @Input = '(108 * 4) + (((36 - 4) * (5)) * 2)'
exec test @Input, @int output

print @int
GO

drop proc test
GO

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

CLOSE AttributeCursor
DEALLOCATE AttributeCursor
GO

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

quote:

---

Originally posted by tduggan

I can't get it to work either. Why can't you compute the value inside the stored proc? Why do you need to output a formula? Can't you just refer to the column names in the computation?

SELECT (Column1 + Column2)/(ColumnA - ColumnB)
FROM Table1

Tara

---

declare @iTemplate int, @fResult varchar(500)
SET @iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @iTemplate, @fResult OUTPUT
SELECT @fResult = 'SELECT ' + @fResult
EXEC (@fResult)

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 

quote:

---

Originally posted by byrmol

Ok I see the problem here..

In your example you use @fResult float instead of @fResult varchar(500)

This works....

declare @iTemplate int, @fResult varchar(500)
SET @iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @iTemplate, @fResult OUTPUT
SELECT @fResult = 'SELECT ' + @fResult
EXEC (@fResult)

DavidM

"If you are not my family or friend, then I will discriminate against you"

---

CREATE PROCEDURE

@iTemplate int,
@fResult float OUTPUT,

AS

declare @cResult varchar(500)

EXECUTE usp_shapes_GetCrossSection @iTemplate, @cResult OUTPUT
SELECT @fResult = 'SELECT @fResult = ' + @cResult
EXEC (@fResult)

GO

quote:

---

Originally posted by Lavos

[Previous Post Deleted]

Crap, I A) need to read the other replies closer, B) not accidently hit submit reply before I get everything typed in when I'm switching between windows to double check code, and C) post quick cause otherwise someone will snipe me (like what happened with my first post that I deleted.) (and probably D) stop editing posts over and over after posting)

Here was my solution, copying the beginning parts from DavidM :)
----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

---