It worked. Now i can have everything on the same procedure.

But i don't understand why it worked....

let me see if it's because of this:

It worked because with dynamic SQL statements the stored procedure doesn't go and see if the columns of that table allready exist. Correct?

Then the stored procedure is compailed and during the compilation the identity column is created.

After that, when i send an execute comand, so that the stored proc is executed, the column is allready there (created during compilation) and therefour, it works fine.

Is this correct?

Other question about the same procedure:

The procedure is below:

alter proc VALIDADOR2

as

set xact_abort on

set nocount on

begin transaction

update bulk_provincia set VALIDO_BULK = 1

where (numprov is not null and idprov is not null and numprov <> '' and idprov <>'')

update bulk_provincia set operacao_bulk ='INSERT' where numprov in

(select numprov from bulk_provincia where valido_bulk=1 and numprov not in(select numprov from provincia))

update bulk_provincia set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1

delete from bulk_provincia where valido_bulk is null

declare @maxprov as int

set @maxprov=(select max(codprov)+1 from provincia)

exec('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY ('+@maxprov+',1)')

update bulk_repfiscal set VALIDO_BULK = 1

where (numrf is not null and idrf is not null and cod_provincia is not null

and numrf <> '' and idrf <>'' and cod_provincia <>'' and cod_provincia in

(select numprov from bulk_provincia union select numprov from provincia))

update bulk_repfiscal set operacao_bulk ='INSERT' where numrf in

(select numrf from bulk_repfiscal where valido_bulk=1 and numrf not in(select numrf from repfiscal))

update bulk_repfiscal set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1

delete from bulk_repfiscal where valido_bulk is null

exec('update bulk_repfiscal set bulk_repfiscal.cod_provincia_bulk=r.codigo

from

(select codprov as codigo, numprov as numero from provincia

union

select codigomaximo as codigo, numprov as numero from bulk_provincia where valido_bulk=1

and operacao_bulk=''INSERT'') as r

where bulk_repfiscal.cod_provincia =r.numero')

declare @maxRep as int

set @maxrep=(select max(codrf)+1 from repfiscal)

exec('ALTER TABLE bulk_repfiscal add CODIGOMAXIMO INT IDENTITY ('+@maxrep+',1)')

if @@trancount >0

begin

commit transaction

end

else

rollback transaction

set nocount off

go

exec validador2

because of this statement below is now a dynamic sql statement:

exec('update bulk_repfiscal set bulk_repfiscal.cod_provincia_bulk=r.codigo

from

(select codprov as codigo, numprov as numero from provincia

union

select codigomaximo as codigo, numprov as numero from bulk_provincia where valido_bulk=1

and operacao_bulk=''INSERT'') as r

where bulk_repfiscal.cod_provincia =r.numero')

will the end of the stored procedure stiill work fine?

I ask this, because i don't know if the fact that i have dynamic SQL statements, creates other transactions.

As you can see in the above stored proc, i started the stored proc with the "Begin transaction"

And end it commiting that transaction if there's no errors. Otherwise , the transaction is rolled back.

Does the fact that this SQL statement is dynamic, afects the number of transaction inside my stored proc? Or it runs as a unique transaction?

tks,

Pedro