ALTER TABLE inside IF statement

Question

ALTER TABLE inside IF statement

JMI

SSChampion

Points: 11831
More actions
April 22, 2005 at 10:11 am

#61549

Hi,
Is this supposed to work?
------------------------
IF (...)
BEGIN
   ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY CLUSTERED...
END
--------------------------
No matter which conditions I add
it always executes ALTER TBALE part first.
Below is full scrips:
######################################################
--make a temp storage for 'clean' data
create table dbo.Tmp_mapRiskMeasure
(
RiskMeasureShortDescr varchar(15) NOT NULL,
SourceSystemID smallint NOT NULL,
RiskMeasureSourceValue varchar(30) NOT NULL
)
--insert grouped records to eliminate duplicates
insert dbo.Tmp_mapRiskMeasure
(
RiskMeasureShortDescr,
SourceSystemID,
RiskMeasureSourceValue
)
select
RiskMeasureShortDescr,
SourceSystemID,
RiskMeasureSourceValue
from mapRiskMeasure
group by
RiskMeasureShortDescr,
SourceSystemID,
RiskMeasureSourceValue
declare
@count1 smallint,
@count2 smallint,
@go tinyint
set @go=0
select @count1 = count(*) from dbo.Tmp_mapRiskMeasure
select * from mapRiskMeasure group by RiskMeasureShortDescr,SourceSystemID,RiskMeasureSourceValue
select @count2 = @@rowcount
--delete the old table with duplicates
if (@count1 = @count2)
begin
  print 'counts match'

  DROP TABLE dbo.mapRiskMeasure

  --rename temp table
  EXECUTE sp_rename N'dbo.Tmp_mapRiskMeasure', N'mapRiskMeasure', 'OBJECT'
  --re-create all indexes and create a new PRIMARY KEY
  CREATE NONCLUSTERED INDEX FK_RskMsrMapRskMsr2 ON dbo.mapRiskMeasure
   (
   RiskMeasureShortDescr
  &nbsp ON [PRIMARY]

  CREATE NONCLUSTERED INDEX FK_SourceSysMapRskMsr ON dbo.mapRiskMeasure
   (
   SourceSystemID
  &nbsp ON [PRIMARY]

  ALTER TABLE dbo.mapRiskMeasure ADD CONSTRAINT
   PK_mapRiskMeasure PRIMARY KEY CLUSTERED
   (
   RiskMeasureShortDescr,
   SourceSystemID,
   RiskMeasureSourceValue
  &nbsp ON [PRIMARY]
end
else
begin
  print 'counts do not match'
  DROP TABLE dbo.Tmp_mapRiskMeasure
  RETURN
end
#####################################################

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

It looks like @@rowcount equals the count from mapRiskMeasure. In other words, @count1 = @count2. There does not seem to be any WHERE on the selections; either to fill the table Tmp_mapRiskMeasure or the SELECT * FROM mapRiskMeasure.

Try populating @count2 in another fashion with PRINT statements instead of your Table statements and stored procedure execution and see if that works. Then you may have to re-think your logic to initiate this flow control... Good luck.

I wasn't born stupid - I had to study.