create procedure proc1 (@a int)
as
begin
if @a=1
create table #temp(a1 int)
else
create table #temp(a1 int)
end
while executeing the above code it shows error
like '#temp already exist' .why it shows an error?
Answers were Sorted based on User's Feedback
Answer / saravanan p
Since the stored procedure precompiled, we cannot give same
table name in both the If and else part.
It will work if give some other name in else part.
Is This Answer Correct ? | 11 Yes | 2 No |
Answer / soorai ganesh
Problem in #temp table. Because it comes twice in SP.
(U can say it is in else part. But compiler will not accept
this.First it will compile everthing then running the SP. )
Its better to keep another name for Else part table.......
Is This Answer Correct ? | 8 Yes | 6 No |
Answer / lee
yep problem is with the compiler.. I had to think of
another way to solve the problem, eg, I created the #temp
without the IF statement and then used an IF ELSE statement
to modify or update #temp, make sense?
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / madhu sudhan g
Hiiii
in Stored Procedures we cannot create same temp table twice if it is in IF else conditions also
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / murthy
The #Temp table created in this stores procedure exists in
memory as long as the Particular session is closed. Hence
drop the table at the end of the procedure.
Note: In case if you exec the SP using Management studio,
close the connection and re-open it, it will work without
modifying anything.
Is This Answer Correct ? | 1 Yes | 3 No |
Answer / utpal chakraboarty
Because, Stored procedure is pre-compiled language.
Is This Answer Correct ? | 4 Yes | 12 No |
Answer / david
it indicates that some one had already executed the stored
proc but forgotten to delete the temp table they have
created.
normally before closing that particular transaction within
the SP we need to use drop table statement.
Is This Answer Correct ? | 1 Yes | 10 No |
Answer / gaurav
Because temp is being used by database internally so that it
is same as a system table so this table can't create by user...
Is This Answer Correct ? | 1 Yes | 14 No |
Difference between Inner vs outer joins?
What is star, snowflake and star flake schema? : sql server analysis services, ssas
Why should we go for stored procedures? Why not direct queries?
The Difference between 'Count' and 'Count(*)'
Magic Tables ?
If we drop a table, does it also drop related objects like constraints, indexes, columns, defaults, views, and stored procedures?
How to check status of stored procedure in sql server?
What are cursors? Explain the different types of cursors Enlist a few disadvantages of cursors.
What are different types of database indexes?
How to provide values to stored procedure parameters in ms sql server?
what is the output for this query select * from employee where 1=1;
What is usually the first word in a sql query?