Multi-Table Function Help please! 1

[ROGERDODGE]

I have tables in SQL Server structured as below ( greatly simplified )

Engines
+++++++
EngineId
Description
ManufacturerId
EngineType
EngineDesign
..
..
etc
..
..
+++++

The two fields EngineType and EngineDesign are values from another table "KeyTableEntries" which hold a set of possible values for these two fields.

The Structure of the KeyTableEntries table is

+++++

TableNo
KeyValue
Description
+++++

The Table Nos which apply to the various types is "hard wired" or pre-defined in the design.

So to get the Display value for say EngineType (defined as Key Table 80) we ;
SELECT Description FROM KeyTableEntries WHERE TableNo = 80 and KeyValue = Engines.EngineType

and for say EngineDesign (defined as Key Table 81)

SELECT Description FROM KeyTableEntries WHERE TableNo = 81 and KeyValue = Engines.EngineDesign

etc.

The basic problem is that there are eleven such values which is going to make the query huge.

I have been looking at UDF's and the idea of creating a Multistatement Table function ie

CREATE FUNCTION dbo.GetEngineData (@EngId int)
RETURNS @Engines TABLE
(
EngineId int,
Description nvarchar(60),
ManufacturerId int,
EngineType nvarchar(20),
EngineDesign nvarchar(20)
)
AS
BEGIN
DECLARE @EngineType nvarchar(20)
DECLARE @EngineDesign nvarchar(20)

INSERT INTO @Engines
SELECT EngineId,Description,ManufacturerId,EngineType,EngineDesign
FROM Engines WHERE EngineId = @EngId

SELECT @EngineType = Description FROM KeyTableEntries WHERE KeyTableEntries.KeyEntry = @Engines.EngineType
AND TableNo = 80

SELECT @EngineDesign FROM KeyTableEntries WHERE KeyTableEntries.KeyEntry = @Engines.EngineDesign
AND TableNo = 81

UPDATE @Engines
SET EngineType = @EngineType, EngineDesign = @EngineDesign

RETURN
END


The second and third SELECT's above fail the Syntax check. If I replace the ' = @Engines.EngineType' with a value then it works.
So, my basic problem is that I cannot seem to reference the @Engines Table even though I have declared it??

Hopefully someone can point out the error of my ways or suggest a better approach.

Roger Maynard

 

[NoCoolHandle]

so you have to ask yourself would the following work if it was a real table..

select firstname from northwind..employees where lastname = employees.lastname

..

YOur problem is either that you need a join to the temp table or you need to save your col value to a variabel and then use the variable in the where clause..

I would tend to think you need a join..


i.e

FROM Engines WHERE  EngineId = @EngId

 SELECT @EngineType = Description FROM KeyTableEntries 
[red]join @engines on KeyTableEntries.KeyEntry = @Engines.EngineType[/red]
WHERE   TableNo = 80

or

[red]Declare @val varchar(3000) [/red]
 INSERT INTO @Engines
 SELECT EngineId,Description,ManufacturerId,EngineType,EngineDesign
 FROM Engines WHERE  EngineId = @EngId

[red]select @val = enginetype from @engines[/red]

 SELECT @EngineType = Description FROM
 KeyTableEntries WHERE KeyTableEntries.KeyEntry = [red]@val[/red]
 AND  TableNo = 80

 

[ROGERDODGE]

When you put it like that !!!

<< Stands back about 20 feet >>

I have declared and filled an @EngineType variable etc.
It now works perfectly.

BIG Thanks!!
Roger