Temporary local table - SQL Server Forums

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)

Temporary local table

Author

Topic

Jaap
Starting Member

19 Posts

Posted - 2008-11-14 : 04:25:36

I have a problem with Temporary local table.
I have several stored procedurers they look like the same

Part 1 is creating a temp. table (#tblArticle)
Part 2 Insert into the temp. table
Part 3 Several Updates on the temp. table (Update the language and the prices depanding on @IDLanguage and IDCustumer)

Part 1 and 3 are in al SP's the same. Only part two is diferent.
Now I would make part 1 and 3 in seperate SP's so I have the code only once.

So my sp looks like this

EXEC sp_Create -- Part 1
INSERT INTO etc -- Part 2
EXEC sp_Update -- Part 3

But this does'nt work. 'Invalid object name #tblArticle' in part 2
Is'nt it posible to create a temp. table in a sp and use it in the main SP?

How can I fix this problem?

Jaap

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-14 : 04:46:53

Why have it in different SPs. Regardless, The hash(temp) table is common to a single instance only. So, what you are doing would give you an error always.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-14 : 04:48:14

Or, you could use a global temporary table instead.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 04:48:27

The #tblArticle table is only available in same scope, because you created a local temp table.
Use ##tblArticle for a global temp table.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-14 : 04:48:52

Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:00:21

Or you create the temp table first.

1) create temp table
2) EXEC sp_Create -- Part 1 (insert only)
3) INSERT INTO etc -- Part 2
4) EXEC sp_Update -- Part 3

E 12°55'05.63"
N 56°04'39.26"

Jaap
Starting Member

19 Posts

Posted - 2008-11-14 : 05:17:17

Hello Sakets_2000

Why do I use different SP's?
Because I need the temp table (part1) and the update's (part 3) more than once.

First I will try to expain what the idee is after this

I need a recordset for a website.
This recordset contains article's and can be in deferent language's
Also the price's can be different for each custumer.

There are posibilities for getting the article's by articleGroup, by collor, by articleID or by catalogNR and so on.
Every time the select is different.
I know I can make one SP with several parameters and then make the wright select but thats not what I like to. I like to have one SP for every select.
So I create the table and insert the selected Articles.
After that I change article-discriptions in the right language and the default price's in the custummer price's.'
After his I do a Select * of the temp. table so the SP returns a recordset to the web application.

When I use ##tblArticle then every user can use this table. The table will not be deleted after the execute of the SP, so I need to drop it every time. Then I have the problemm when more users are using one of the SP's that uses this table the table of user one will be droped by user two. Thats not what I want.

Or I am wrong with this?

Jaap

ps thanks for all reactions on this moment so quickly.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-14 : 05:52:25

You can have one sp for every select. It'll work fine as long as you are creating,inserting and updating using the same session id.
Is it possible to have it all together in your case ?

Also, How much data are we talking about ? You could go for a table variable also incase it suits you and your requirements.

Jaap
Starting Member

19 Posts

Posted - 2008-11-14 : 06:21:54

Hello Sakets_2000

Thanks for your help.
I fixed it another way.
My input parameters have always the same data type

@IDCustumer int
@ID... int (In the diferent SP's IDGroup, IDColor, IDArticle are all int's)
@IDLanguage Char(2)

So I made it now
@IDCustumer int
@ID int
@SelectOption Int
@IDLanguage Char(2)

SelectOption =
1 for IDGroup selection (8000 articles over 25-35 groups)
2 for IDColor selection (8000 articles 10-15 colors. 40% have IDColor IS NULL)
3 for IDArticle selection (unique key returns 1)
4 for IDCatalogNr selection (should be unique returns 1)

Now I have one SP that creates a temp table, insert and update and returns a recodset

Jaap

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-14 : 06:27:39

right, one sp for it all should work just fine. Thats what I meant by "Why have it in different SPs"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources