If else issue

Question

If else issue

ak08

Mr or Mrs. 500

Points: 543
More actions
September 25, 2013 at 6:27 pm

#283141

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ParulPrabu SSCrazy Points: 2271 More actions · Answer 1

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