Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

If else issue Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 6:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 9:34 AM
Points: 29, Visits: 383
Declare @Version int;




select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))


if (@version >=11)

begin
IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL

DROP TABLE #checkresults


create table #checkresults
(

[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](650) NULL,
[RepairLevel] [varchar](150) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[DbFragId] [int] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
[RidDbId] [int] NULL,
[RidPruId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefDbId] [int] NULL,
[RefPruId] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL )

end


else



begin


IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL

DROP TABLE #checkresults

create table #checkresults
(

[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](650) NULL,
[RepairLevel] [varchar](150) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
--[DbFragId] [int] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
--[RidDbId] [int] NULL,
--[RidPruId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
--[RefDbId] [int] NULL,
--[RefPruId] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL )

end


select * from #checkresults


Msg 2714, Level 16, State 1, Line 59
There is already an object named '#checkresults' in the database.


this is the error i am getting can u please help me
Post #1498637
Posted Wednesday, September 25, 2013 11:25 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:37 PM
Points: 1,454, Visits: 136
SQL parses your script and finds that you are attempting to create it in two different places, and so raises an error, its better to create the table in the beginning with common columns and add the specific columns in the if--else based on the condition

Declare @Version int;




select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))



IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL

DROP TABLE #checkresults

create table #checkresults
(

[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](650) NULL,
[RepairLevel] [varchar](150) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
--[DbFragId] [int] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [int] NULL,
[AllocUnitId] [int] NULL,
--[RidDbId] [int] NULL,
--[RidPruId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
--[RefDbId] [int] NULL,
--[RefPruId] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL )
if (@version >=11)

begin
alter table #checkresults add
[DbFragId] [int] NULL,
[RidDbId] [int] NULL,
[RidPruId] [int] NULL,
[RefDbId] [int] NULL,
[RefPruId] [int] NULL

end


drop table #checkresults

Post #1498671
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse