Force statement order in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Force statement order in stored procedure

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-05 : 15:37:14

Hi!
In this stored procedure, which copies all procedures from one insurance to another, there is the following problem: The Alter table doesn't seem to run before the INSERT because the error returned is "An explicit value for the identity column in table 'CPTCodes' can only be specified when a column list is used and IDENTITY_INSERT is ON." But if I run all the lines one-by-one in QA, it will go through OK. How do I ensure the Alter table goes throught before the insert?

CREATE Procedure CopyCPT @OldInsCo CHAR(10),@NewInsCo CHAR(10),@Action TINYINT
AS
BEGIN
SELECT * INTO #CopyCPTCodes FROM CPTCodes WHERE InscoID = @OldInsCo
UPDATE #CopyCPTCodes SET InscoID = @NewInsCo
ALTER TABLE #CopyCPTCodes DROP COLUMN RowID --drop identity column
INSERT INTO CPTCodes SELECT * FROM #CopyCPTCodes
END
RETURN

Thanks!

Sarah Berger MCSD

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 15:41:40

How about issuing a CHECKPOINT command after the alter table command?

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-06 : 10:51:18

Hi,

The CHECKPOINT command doesn't seem to help any. I still get the same error message.

Any other ideas?

TIA,
Sarah

Sarah Berger MCSD

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-06 : 10:58:20

Where are you putting it? I would think it would need to go after the update statement, and another one after the alter statement.

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-06 : 11:02:42

Also, are you using any begin transaction statements? If so the checkpoint command won't work within a transaction (I think...researching that one.)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-06 : 11:06:52

Yes, I tried it with CHECKPOINT in both the locations you've mentioned (still getting the error in Query Analyzer). And no, I'm not using any transaction statement. Just a BEGIN and END within the IF statement.

What else?!

Sarah Berger MCSD

izaltsman
A custom title

1139 Posts

Posted - 2002-03-06 : 11:11:16

I would issue ALTER TABLE via a dynamic SQL call:

EXEC ('ALTER TABLE #CopyCPTCodes DROP COLUMN RowID')

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-03-06 : 11:17:57

Hey, Thanks! That EXEC line did the trick!

Sarah Berger MCSD

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-06 : 11:20:58

As a learning resource for us all perhaps izaltsman could explain why the EXEC statement would work where as the regular SQL statement would not? I am curious.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-06 : 12:01:18

quote:

As a learning resource for us all perhaps izaltsman could explain why the EXEC statement would work where as the regular SQL statement would not? I am curious.

It has to do with when the statements are compiled. When it is not dynamic all the steps are compiled at once and the Insert statement cannot be fulfilled under the current structure. When you introduce dynamic sql the statements are compiled seperately.. And Success.

A few comments on the original question:
1. Does you production code really not list columns for SELECTs and INSERTs. This is likely to drive you nuts at some point.

2. I understand the cod in your post is probably a simplification, but you can accomplish what is in the test code in one simple statement if you are willing/able to list ColumnNames:


CREATE Procedure CopyCPT @OldInsCo CHAR(10),@NewInsCo CHAR(10),@Action TINYINT
AS
BEGIN


INSERT INTO CPTCodes (InscoID, Col2,...Coln)
SELECT @NewInsCo, COl2,...Coln
FROM CPTCodes 
WHERE InscoID = @OldInsCo

END
RETURN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources