Using a temporary variable in a query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Using a temporary variable in a query

Author

Topic

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-10 : 12:04:50

I want to select a value (call it "code") from table X based on a parameter, then use "code" to select a value from table Y. The returned value from table Y is what the S.P. is going to return. I'm trying to use a temporary table as a variable to hold "code" before making my second selection.

Alter Procedure [dbo].[Flex_RPT_CertItemCerts] (
@item nvarchar(15)
)

AS

/*Create Temporary table to hold code*/
Create Table #MatCode
(
code nvarchar(25)
)
insert into #MatCode.code
Select item.charfld1
From item
Where item.item = @item

/*Select ShortDescription Based on code from #MatCode*/
Select MatDescriptions.ShortDescription
From MatDescriptions
Where MatDescription.MaterialCode = #MatCode.code

I get no results and the following error:Msg 208, Level 16, State 1, Procedure Flex_RPT_CertItemCerts, Line 17
Invalid object name '#MatCode.code'.

Is there a more direct way to use a temporary variable or make the temp table work?

Example tables:
item:
Item-------charfld1
8160-------222
8170-------555
8365-------999

MatDescription:
MaterialCode-----ShortDescription
222---------------LabGrade
555---------------Industrial
999---------------Petroleum

So an input to the code above of "8170" should return "Industrial"
There are no duplicate rows in either table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-10 : 12:22:22

No need for more steps, just do this select:
select md.ShortDescription
from MatDescriptions md
join item it on md.MaterialCode=it.charfld1 and it.Item=@item

No, you're never too old to Yak'n'Roll if you're too young to die.

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-10 : 12:48:21

tried the new select statement. I still get this error code when I run it:

Msg 208, Level 16, State 1, Procedure Flex_RPT_CertItemCerts, Line 16
Invalid object name '#MatCode.code'.

Could there be something wrong with my Temporary Table?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-10 : 13:20:40

There is no need for a temporary table at all...

webfred has "joined" your tables MatDescriptions and item to get the short description. There is no temp table needed to store the code in the interim.

trackjunkie
Starting Member

31 Posts

Posted - 2009-09-10 : 13:41:40

Ah, I misunderstood. It works now. Thanks all.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-10 : 14:03:46

welcome...Just replied to make my post count get past 666...that number scares me..