Stored procedures and UDF help please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Stored procedures and UDF help please?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-04-08 : 09:53:57

Using the following DDL and DML, a cross sectional area shoule be returned for a shape. The result I am getting is NULL.

Any help?
The DDL and DML will create 3 tables, 2 stored procs and 1 UDF.


USE NORTHWIND 
GO

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

INSERT INTO tbProductTemplates (fkProductCode, Template, fkMixID)
SELECT 2001, 1, 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, 10
GO

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

INSERT INTO tbShapeAttributes (AttributeID, fkShapeID, Attribute)
SELECT 1, 1, 'Width' UNION ALL
SELECT 1, 2, 'Height' 
GO

CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)

RETURNS float

AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO

CREATE PROCEDURE usp_shapes_GetRectangularXSection

@iTemplate int,
@fResult float OUTPUT

AS 

declare @fWidth float, @fHeight float

SELECT @fWidth = SUM(CASE sa.Attribute WHEN 'Width' THEN av.AttributeValue ElSE 0 END),
	@fHeight = SUM(CASE sa.Attribute WHEN 'Height' THEN av.AttributeValue ElSE 0 END)
FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa
ON av.fkAttributeID = sa.AttributeID
WHERE av.fkTemplateID = @iTemplate

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
GO

CREATE PROCEDURE usp_shapes_GetXSection

@iProductCode int,
@iTemplateID int,
@fResult float OUTPUT

AS

if(@iProductCode = 2001)
	EXECUTE usp_shapes_GetRectangularXSection @iTemplateID, @fResult
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection 2001, 1, @fResult
SELECT @fResult

GO

DROP TABLE tbProductTemplates
GO

DROP TABLE tbProductTemplateAttributeValues
GO

DROP TABLE tbShapeAttributes
GO

DROP PROCEDURE usp_shapes_GetRectangularXSection
GO

DROP PROCEDURE usp_shapes_GetXSection
GO

DROP FUNCTION RECTANGULAR_XSECTION
GO

I am getting a NULL result. Any ides?

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-04-08 : 10:07:08

Correction to previous post:
INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue)
SELECT 1, 1, 108 UNION ALL
SELECT 1, 2, 10

Should be

INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue)
SELECT 1, 1, 108 UNION ALL
SELECT 2, 1, 10

*********************************************************************
** New infomation
CREATE PROCEDURE usp_shapes_GetRectangularXSection

@iTemplate int,
@fResult float OUTPUT

AS

declare @fWidth float, @fHeight float

SELECT @fWidth = SUM(CASE sa.Attribute WHEN 'Width' THEN av.AttributeValue ElSE 0 END),
@fHeight = SUM(CASE sa.Attribute WHEN 'Height' THEN av.AttributeValue ElSE 0 END)
FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa
ON av.fkAttributeID = sa.AttributeID
WHERE av.fkTemplateID = @iTemplate

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS OkHere
GO

CREATE PROCEDURE usp_shapes_GetXSection

@iProductCode int,
@iTemplateID int,
@fResult float OUTPUT

AS

if(@iProductCode = 2001)
BEGIN
EXECUTE usp_shapes_GetRectangularXSection @iTemplateID, @fResult
SELECT @fResult AS XSection END
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection 2001, 1, @fResult
SELECT @fResult AS Result

[/code]

In the above, the green is the correct answer, the read and blue are NULL. So the value isn't begin passed through the output parameters correctly?

What am I doing wrong?

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-04-08 : 11:15:08

Easier code to use and read. Minus the tables!


USE NORTHWIND 
GO

CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)

RETURNS float

AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO


CREATE PROCEDURE usp_shapes_GetRectangularXSection

@fResult float OUTPUT

AS

declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
GO

CREATE PROCEDURE usp_shapes_GetXSection

@fResult float OUTPUT

AS

EXECUTE usp_shapes_GetRectangularXSection @fResult
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as Result
GO

DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO

Any ideas. This should not be returning NULL as the Result.

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-04-08 : 11:44:46

I found it, I forgot "OUTPUT" on the call to the stored procedure!!

Damn, simple, simple, simple!!!!
Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources