Error while creating temp table

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

General SQL Server Forums

New to SQL Server Programming

Error while creating temp table

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-10 : 00:48:23

hello friends!!

i am trying to create stored procedure but i am getting error

create proc t
@i int
as

if @i = 1
begin
select s Name,identity (int,1,1) as intid into #T
from
(
select 'SS' s) p
end
if @i = 2
begin
select s Name,identity (int,1,1) as intid into #T
from
(
select 'S' s) p
end

Server: Msg 2714, Level 16, State 1, Procedure t, Line 15
There is already an object named '#T' in the database.
Server: Msg 170, Level 15, State 1, Procedure t, Line 17
Line 17: Incorrect syntax near 'p'.

T.I.A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 00:53:32

Create #T first, before all IFs.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-10 : 01:02:34

You can't have more than one insert into statement that create the same temp table.

As Peter suggested create the temp table first or use different temp table name

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-10 : 01:26:48

hello ppls..

thanks for ur kindness!!!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-10 : 09:28:27

It is parsing error

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 09:36:13

Parsing error?

create proc t
@i int
as

if @i = 1
begin
select s Name,identity (int,1,1) as intid into #T1
from 
(
select 'SS' s) p
end
if @i = 2
begin
select s Name,identity (int,1,1) as intid into #T2
from 
(
select 'S' s) p
end

works well...

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-10 : 09:53:17

I think it's some kind of weird bug...why can't it allow to create temp table based on two different conditions? There is no chance of creating same table twice..I mean syntactically there is no error !

Harsh Athalye
India.
"Nothing is Impossible"

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-10 : 10:09:03

....and this is an exercise in?


create proc t
@i int
as

DECLARE @T table ([Name] char(2), intid int IDENTITY(1,1))

IF @i = 1
	INSERT INTO @T([Name]) SELECT 'SS'

IF @i = 2
	INSERT INTO @T([Name]) SELECT 'S'

SELECT * FROM @T
GO

EXEC t 1
GO

EXEC t 2
GO

DROP PROC t
GO

Futility? Want to give us what you are really going for?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-10 : 10:10:36

Peso, use #T1 in two conditions

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-10 : 10:12:36

Yes we all se that the sproc reacts as though you are trying to create 2 temp tables on compile.

There's is no need to worry about trans logging for one row. He should just use a table variable.

And until they tell us what they really want, the code is rather meaningless

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources