"jack" <gautams.mail@gmail.com> wrote in message
news:1168150646.403090.318850@51g2000cwl.googlegroups.com...
> Hi All,
> Im creating a temporary table in a procedure in an if condition but it
> is giving a error like table already created.
> What i want is that depending upon the condition the result in the
> table data should be displayed. below is the procedure but gives me
> error.
>
> -------------------------------------------
> create procedure usps_getTrans
> txn_yyyymm int
> as
>
> if (txn_yyyymm =200607)
> begin
> select txn_yyyymm, UnitCode, Amount
> into #temp from Transaction
> where txn_yyyymm = 200607
> end
>
> if (txn_yyyymm =200608)
> begin
> select txn_yyyymm, UnitCode, GLCode Amount
> into #temp from Transaction
> where txn_yyyymm = 200608
> end
>
> select txn_yyyymm, UnitCode, Amount from #temp
> -------------------------------------------
> Server: Msg 2714, Level 16, State 1, Procedure usps_getTrans, Line 15
> There is already an object named '#temp' in the database.
> Server: Msg 170, Level 15, State 1, Procedure usps_getTrans, Line 2
> Line 2: Incorrect syntax near 'txn_yyyymm'.
> Server: Msg 156, Level 15, State 1, Procedure usps_getTrans, Line 8
> Incorrect syntax near the keyword 'Transaction'.
>
>
> The error shows that the table is already created. Im not able to find
> the solution for this as i want to create only one temp table rather
> than creating two tables.
>
> Some one suggested me to do truncate table before filling the second
> table in if condition. will this help.
>
>
>
> Thanks for replying me..
>

> I tried using below statement but couldnt make it
>
> if object_idn('#temp') is not null
> drop table #temp

> It should be object_id('tempdb..#temp') IS NOT NULL

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98B17C9D721D1Yazorman@127.0.0.1...
> jack (gautams.mail@gmail.com) writes:
>> I tried using below statement but couldnt make it
>>
>> if object_idn('#temp') is not null
>> drop table #temp
>
> It should be object_id('tempdb..#temp') IS NOT NULL
>
> However, I strongly recommend that you create the table with CREATE TABLE,
> and stick with one definition within the procedure. If create the table
> with different columns depending on conditions, you are walking a very
> difficult path, and you are likely to run into compilation errors, as the
> procedure is recompiled, particularly on SQL 2000.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx