How to use select,alter,insert into select statements one after another in the same batch?

Question

Viewing 6 posts - 1 through 7 (of 7 total)

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

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

GO is an execute command for T-SQL

after a GO, all variables are lost. Just as happens when you execute different queries in QA.

N 56°04'39.16"
E 12°55'05.25"

Bagath SSCrazy Points: 2409 More actions · Answer 2

But how to get the criteria with in that procedure....?

is there any other way...to do like that?

Thanks,

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

'GO' is the token to terminate the batch, so there is no way that you can have 'GO' inside a procedure.

When you complie the proc, the 'GO' will end the proc, and code after that will not get compiled as part of that proc.

Could you expand a bit on your real problem? What is it you need to do? Do you really need a 'GO' in the middle?

If you can provide some example and description about your problem, there may be other options.

/Kenneth

Bagath SSCrazy Points: 2409 More actions · Answer 4

I dont want to use GO and know that all variables expire after that....i have given that code for ex only....

In my proc first one temp table will be create,have to alter that table(Need to add column) and after that insert some values using the variables.

using linked server i am creating and inserting values...

The actual action i want is as below......

declare @c int

set @c=7

select @a=a @b-2=b from temp

alter table temp add c

truncate table temp

insert into temp select @a,@b,@c

Did u get what my plan is?

Thanks,

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

You may have to adjust or rethink your processing, since what you're trying to do won't work inside the same batch.

-- snip from BOL paragraph Batches ---

A table cannot be altered and then the new columns referenced in the same batch.

-- end snip --

Is there any reason that the added column can't be there to start with, so you don't have to add it in the middle of the work?

/Kenneth

Bagath SSCrazy Points: 2409 More actions · Answer 6

Ok...Thanks for ur information.....

I will use another temp table to get the data....

Thanks to all............

Rao Aregaddan.