June 18, 2012 at 6:37 am
Hi Guys,
I want that the DML of some stored procedures can only be executed against the database, if an entire begin try end try get no errors.
I have created a stored procedure named spSGCT_IMPORTA_SGCTLOCAIS
As the following code:
LTER PROCEDURE [dbo].[spSGCT_IMPORTA_SGCTLOCAIS] (
@DATA1 AS DATETIME,
@NOMEFICHEIRO AS VARCHAR(500),
@CODRF AS VARCHAR(5),
@INICIO AS VARCHAR(10),
@fim AS BIGINT,
@DB AS VARCHAR(50),
@CLASSIFICACAO AS INT
)
AS
BEGIN
IF (SELECT COUNT(*) FROM FICHEIROSIMPORTAR WHERE VALIDO =1) <> 0
BEGIN
EXEC dbo.spSGCT_BULKLOAD_SGCTLocais @CODRF
EXEC dbo.ALTER_TABELAS_BULK
EXEC dbo.sp_SGCT_VALIDACTBS_SGCTLocais @DB
begin try
begin tran
EXEC dbo.spSGCT_CONTRIBUINTES_ALTERADOS @DB
EXEC dbo.spSGCT_CONTRIBUINTES_NOVOS @DB
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS @DATA1,
getdate,
@NOMEFICHEIRO,
@CODRF,
@INICIO,
@fim,
@DB,
@CLASSIFICACAO
commit tran
end try
begin catch
rollback tran
end catch
END
END
So, What I want is that this three procedures:
EXEC dbo.spSGCT_CONTRIBUINTES_ALTERADOS @DB
EXEC dbo.spSGCT_CONTRIBUINTES_NOVOS @DB
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS @DATA1,....
can only execute their dml if the three have sucess.
Inside each of this three procedures I have put a begin try and end try and a catch
as below:
Code of dbo.spSGCT_CONTRIBUINTES_ALTERADOS:
ALTERPROCEDURE [dbo].[spSGCT_CONTRIBUINTES_ALTERADOS] (@DB AS VARCHAR(50))
AS
DECLARE @STRSQL AS VARCHAR(8000)
BEGIN TRY
BEGIN TRAN
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ACT_SECUNDARIAS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ACTIVIDADES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.CONTRIB_IMPOSTOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.EMPRESA
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ESCRITORIOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.GERENTES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.MATRICULAS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.SINGULARES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.SOCIOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SGCT300_CTB_RF_CHANGED
SELECT A.NIF_ANTIGO
FROM
(SELECT * FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+''')) A,
'+@DB+'.dbo.CONTRIBUINTES B
WHERE
A.NIF_ANTIGO = B.NIF_ANTIGO
AND
A.COD_REP_FISCAL <> B.COD_REP_FISCAL'
EXEC (@STRSQL)
SET @STRSQL = 'UPDATE '+@DB+'.[dbo].[CONTRIBUINTES]
SET [NIF] = A.NIF
,[FILIAL_NUMBER] = A.FILIAL_NUMBER
,[STATUS] = A.STATUS
,[COD_GRP_CONTRIBUINTE] = A.COD_GRP_CONTRIBUINTE
,[COD_MET_TRIBUTARIO] = A.COD_MET_TRIBUTARIO
,[COD_TP_SINGULARES] = A.COD_TP_SINGULARES
,[COD_TP_IMPOSTOS] = A.COD_TP_IMPOSTOS
,[GRANDE_CONTRIBUINTE] = A.GRANDE_CONTRIBUINTE
,[NOME] = A.NOME
,[MORADA] = A.MORADA
,[C_POSTAL] = A.C_POSTAL
,[TELEFONE] = A.TELEFONE
,[FAX] = A.FAX
,[COD_PROVINCIA] = A.COD_PROVINCIA
,[COD_MUNICIPIO] = A.COD_MUNICIPIO
,[COD_REP_FISCAL] = A.COD_REP_FISCAL
,[COD_COMUNA] = A.COD_COMUNA
,[DT_INICIO] = A.DT_INICIO
,[DT_ALTERACAO] = A.DT_ALTERACAO
,[DT_CESSACAO] = A.DT_CESSACAO
,[COD_MOT_CESSACAO] = A.COD_MOT_CESSACAO
,[MOT_CESSACAO] = A.MOT_CESSACAO
,[COD_TP_INSTITUICAO] = A.COD_TP_INSTITUICAO
,[COD_FUNC] = NULL
,[COD_BAIRRO] = A.COD_BAIRRO
,[COD_CIDADE] = A.COD_CIDADE
,[OBS] = A.OBS
,[DT_BEGIN] = A.DT_BEGIN
,[NUMEROPOSTO] = ''SGCT300''
FROM
(SELECT * FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+''')) A,
'+@DB+'.[dbo].[CONTRIBUINTES] b
WHERE
b.NIF_ANTIGO = A.NIF_ANTIGO'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ACT_SECUNDARIAS
SELECT A.NIF_ANTIGO, A.COD_CAE, B.COD_ID, GETDATE(),''SGCT300'',null,null
FROM
BULK_ACT_SECUNDARIAS A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ACTIVIDADES
SELECT A.NIF_ANTIGO,B.CODIGO,B.DESCRICAO,B.INDEX_POS,''SGCT300'',GETDATE(),null,null
FROM
BULK_ACTIVIDADES A,
'+@DB+'.dbo.TP_ACTIVIDADES B
WHERE
A.INDEX_POS = B.INDEX_POS
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.CONTRIB_IMPOSTOS
SELECT
NIF_ANTIGO,
COD_IMPOSTO,
GETDATE(),
''SGCTCENTRA'',
null,
null
FROM
BULK_CONTRIB_IMPOSTOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.EMPRESA
SELECT A.[NIF_ANTIGO]
,A.[DT_CONSTITUICAO]
,A.[DESIGNACAO_COMERCIAL]
,A.[NUM_DR]
,A.[DT_DR]
,A.[COD_ENQUADRAMENTO]
,B.COD_ID
,A.[COD_CAE]
,A.[PERC_CS_PUBLICO]
,A.[PERC_CS_PRIVADO]
,A.[PERC_CS_ESTRANGEIRO]
,A.[CAPITAL_SOCIAL]
,A.[TOTAL_HOMENS]
,A.[TOTAL_MULHERES]
,A.[VOL_NEGOCIOS]
,A.[TOTAL_ESTABELECIMENTOS]
,A.[NOME_TOC]
,A.[NIF_TOC]
,A.[MORADA_TOC]
,A.[OBS]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_EMPRESA A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ESCRITORIOS
SELECT
[NIF_ANTIGO]
,0
,[NOME]
,[MORADA]
,[LOCALIDADE]
,[COD_REP_FISCAL]
,[COD_PROVINCIA]
,[COD_MUNICIPIO]
,[ARRENDADO]
,
,[SENHORIO]
,[VALOR]
,[TELEFONE]
,[FAX]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_ESCRITORIOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.GERENTES
SELECT
[NIF_ANTIGO]
,[NOME]
,[MORADA]
,[NIF_GERENTE]
,[COD_GERENTE]
,GETDATE()
,''SGCT300''
,null
,null
FROM
dbo.BULK_GERENTES
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.MATRICULAS
SELECT
NIF_ANTIGO,
MATRICULA,
GETDATE(),
''SGCT300'',
null,
null
FROM
BULK_MATRICULAS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SINGULARES
SELECTA.[NIF_ANTIGO]
,A.[BI]
,A.[PENSIONISTA]
,A.[NIF_ENT_PATRONAL]
,NULL
,A.[COD_PROFISSAO]
,NULL
,A.[ACTIVID_DESDE]
,A.[LOCALIZACAO]
,A.[MORADA]
,A.[COD_PROVINCIA]
,A.[COD_MUNICIPIO]
,A.[TEM_EMPREGADOS]
,NULL
,A.[OUTRO_ESTAB]
,A.[PSERVTCO]
,A.[PSERVTCP]
,A.[PSERVA]
,A.[TIPO_EMP_CONTA_PROP_DATA]
,A.[TIPO_EMP_CONTA_PROP_OUTRA]
,A.[TIPO_EMP_CONTA_PROP_OUTRA_NIF]
,NULL
,A.[COD_TP_DOC_IDENTIFICACAO]
,A.[DTACESSACAOTCO]
,A.[DTACESSACAOTCP]
,A.[DTACESSACAOA]
,B.[COD_ID]
,A.[COD_CAE]
,A.[OLDNIF]
,A.[OBS]
,A.[MOTCESSTCP]
,A.[MOTCESSTCO]
,A.[MOTCESSAA]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_SINGULARES A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SOCIOS
SELECT [NIF_ANTIGO]
,[NOME]
,[NIF_SOCIO]
,[MORADA]
,NULL
,[COD_SOCIO]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_SOCIOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
As you can see I have a begin tran and a commit tran too inside each procedure.
So, What I want to know is:
I have a procedure that as three procedures inside.
I want that only if the three procedures rase no error the informations is saved into the database so I used nested begin transactions and commit transactions.
Is this possible?
June 18, 2012 at 8:35 am
Once you hit your rollback this will fail. Your catch inside one of the nested procs will cause the transaction to be rolled back. Your outer try catch will fail because the tran count will be different. Look at the following simplified version of what you are running. This is a single proc with a try-catch and a section of t-sql with a try-catch. It will demonstrate the issue.
create table ProcTable1
(
ProcID int identity,
ProcVal varchar(10)
)
go
create procedure Proc1
as begin
begin try
begin transaction
insert ProcTable1
select 'Explicit fail due to truncation will occur.'
commit transaction
end try
begin catch
select 'Insert failed'
rollback transaction
end catch
end
go
begin transaction
exec Proc1
select * from ProcTable1
commit transaction
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 8:55 am
hum.....
But I have a begin transaction
ands commit transaction on the central procedure (which calls the other procedures).
How can I solve this?
June 18, 2012 at 9:18 am
You will have to modify your existing procs a little to handle this. Below is how you could modify my previous example to make this work. This will still execute the proc by itself correctly with transactions but will basically ignore them outside.
alter procedure Proc1
as begin
declare @TranCount int = @@TRANCOUNT
begin try
if(@TranCount = 0 ) --now we only begin this transaction if the current connection has not yet started a transaction
begin transaction
insert ProcTable1
select 'Explicit fail due to truncation will occur.'
if(@TranCount = 0 ) --commit only if this proc started the transaction
commit transaction
end try
begin catch
if(@TranCount = 0 ) --rollback only if thie proc started the transaction
rollback transaction
else
Raiserror('Error raised in Proc1 catch block.', 16, 1)
end catch
end
go
begin try
begin transaction
exec Proc1
commit transaction
end try
begin catch
rollback transaction
select 'Failed inside Proc1'
end catch
Hope that helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 9:20 am
In reality nested transactions are a myth. http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282630%29-nested-transactions-are-real.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 9:32 am
create table ProcTable1
(
ProcID int identity,
ProcVal varchar(10)
)
go
ALTER procedure Proc1
as begin
begin try
begin transaction outer1
insert ProcTable1
select 'PASS1'
--
begin try
begin transaction inner1
insert ProcTable1
select 'PASS2'
commit transaction inner1
--
begin try
begin transaction inner2
insert ProcTable1
select 'PASS3xxxxxxxxx'
commit transaction inner2
end try
begin catch
select '3nd Insert failed'
rollback transaction inner2
end catch
--
end try
begin catch
select '2nd Insert failed'
rollback transaction inner1
end catch
--
commit transaction outer1
end try
begin catch
select '1st Insert failed'
rollback transaction outer1
end catch
end
go
exec Proc1
select * from ProcTable1
June 18, 2012 at 9:41 am
thadeushuck (6/18/2012)
create table ProcTable1(
ProcID int identity,
ProcVal varchar(10)
)
go
...snip...
exec Proc1
select * from ProcTable1
You really should read the article I posted above. Nested transactions are lie, myth, whatever you want to call it. It does not work like the syntax sounds like it should.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 9:50 am
To prove my point I altered my proc with yours above. Then to test the nested transactions is quite simple.
begin transaction
exec proc1
commit transaction
It doesn't work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 2:29 pm
There is no other way of doing this?
Needs to be with the transaction count?
June 18, 2012 at 2:34 pm
Well if you rollback inside your other proc it will rollback all the "nested" transactions. Then your trancount will be off and it will fail. There are probably some other ways to do it but they are kind of kludgey like what I posted. 😉
All the trancount does it conditionally begin/rollback when a transaction has already been started. Not the most elegant thing but it works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 3:15 am
What about if I don't put a
begin tran
commit tran
inside each separated procedure and put the begin and commit tran only in the master procedure (the one that executes the other procedures).
For example:
ALTER PROCEDURE [dbo].[spSGCT_IMPORTA_SGCTLOCAIS] (
@DATA1 AS DATETIME,
@NOMEFICHEIRO AS VARCHAR(500),
@CODRF AS VARCHAR(5),
@INICIO AS VARCHAR(10),
@fim AS BIGINT,
@DB AS VARCHAR(50),
@CLASSIFICACAO AS INT
)
AS
BEGIN
IF (SELECT COUNT(*) FROM FICHEIROSIMPORTAR WHERE VALIDO =1) <> 0
BEGIN
EXEC dbo.spSGCT_BULKLOAD_SGCTLocais @CODRF
EXEC dbo.ALTER_TABELAS_BULK
EXEC dbo.sp_SGCT_VALIDACTBS_SGCTLocais @DB
begin try
begin tran
EXEC dbo.spSGCT_CONTRIBUINTES_ALTERADOS @DB
EXEC dbo.spSGCT_CONTRIBUINTES_NOVOS @DB
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS @DATA1,
getdate,
@NOMEFICHEIRO,
@CODRF,
@INICIO,
@fim,
@DB,
@CLASSIFICACAO
commit tran
end try
begin catch
rollback tran
end catch
END
END
No begin tran or commit tran inside the procedures:
EXEC dbo.spSGCT_CONTRIBUINTES_ALTERADOS @DB
EXEC dbo.spSGCT_CONTRIBUINTES_NOVOS @DB
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS
This means that the procedure EXEC dbo.spSGCT_CONTRIBUINTES_ALTERADOS @DB
would have it's code changed from:
ALTERPROCEDURE [dbo].[spSGCT_CONTRIBUINTES_ALTERADOS] (@DB AS VARCHAR(50))
AS
DECLARE @STRSQL AS VARCHAR(8000)
BEGIN TRY
BEGIN TRAN
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ACT_SECUNDARIAS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ACTIVIDADES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.CONTRIB_IMPOSTOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.EMPRESA
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ESCRITORIOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.GERENTES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.MATRICULAS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.SINGULARES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.SOCIOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SGCT300_CTB_RF_CHANGED
SELECT A.NIF_ANTIGO
FROM
(SELECT * FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+''')) A,
'+@DB+'.dbo.CONTRIBUINTES B
WHERE
A.NIF_ANTIGO = B.NIF_ANTIGO
AND
A.COD_REP_FISCAL <> B.COD_REP_FISCAL'
EXEC (@STRSQL)
SET @STRSQL = 'UPDATE '+@DB+'.[dbo].[CONTRIBUINTES]
SET [NIF] = A.NIF
,[FILIAL_NUMBER] = A.FILIAL_NUMBER
,[STATUS] = A.STATUS
,[COD_GRP_CONTRIBUINTE] = A.COD_GRP_CONTRIBUINTE
,[COD_MET_TRIBUTARIO] = A.COD_MET_TRIBUTARIO
,[COD_TP_SINGULARES] = A.COD_TP_SINGULARES
,[COD_TP_IMPOSTOS] = A.COD_TP_IMPOSTOS
,[GRANDE_CONTRIBUINTE] = A.GRANDE_CONTRIBUINTE
,[NOME] = A.NOME
,[MORADA] = A.MORADA
,[C_POSTAL] = A.C_POSTAL
,[TELEFONE] = A.TELEFONE
,[FAX] = A.FAX
,[COD_PROVINCIA] = A.COD_PROVINCIA
,[COD_MUNICIPIO] = A.COD_MUNICIPIO
,[COD_REP_FISCAL] = A.COD_REP_FISCAL
,[COD_COMUNA] = A.COD_COMUNA
,[DT_INICIO] = A.DT_INICIO
,[DT_ALTERACAO] = A.DT_ALTERACAO
,[DT_CESSACAO] = A.DT_CESSACAO
,[COD_MOT_CESSACAO] = A.COD_MOT_CESSACAO
,[MOT_CESSACAO] = A.MOT_CESSACAO
,[COD_TP_INSTITUICAO] = A.COD_TP_INSTITUICAO
,[COD_FUNC] = NULL
,[COD_BAIRRO] = A.COD_BAIRRO
,[COD_CIDADE] = A.COD_CIDADE
,[OBS] = A.OBS
,[DT_BEGIN] = A.DT_BEGIN
,[NUMEROPOSTO] = ''SGCT300''
FROM
(SELECT * FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+''')) A,
'+@DB+'.[dbo].[CONTRIBUINTES] b
WHERE
b.NIF_ANTIGO = A.NIF_ANTIGO'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ACT_SECUNDARIAS
SELECT A.NIF_ANTIGO, A.COD_CAE, B.COD_ID, GETDATE(),''SGCT300'',null,null
FROM
BULK_ACT_SECUNDARIAS A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ACTIVIDADES
SELECT A.NIF_ANTIGO,B.CODIGO,B.DESCRICAO,B.INDEX_POS,''SGCT300'',GETDATE(),null,null
FROM
BULK_ACTIVIDADES A,
'+@DB+'.dbo.TP_ACTIVIDADES B
WHERE
A.INDEX_POS = B.INDEX_POS
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.CONTRIB_IMPOSTOS
SELECT
NIF_ANTIGO,
COD_IMPOSTO,
GETDATE(),
''SGCTCENTRA'',
null,
null
FROM
BULK_CONTRIB_IMPOSTOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.EMPRESA
SELECT A.[NIF_ANTIGO]
,A.[DT_CONSTITUICAO]
,A.[DESIGNACAO_COMERCIAL]
,A.[NUM_DR]
,A.[DT_DR]
,A.[COD_ENQUADRAMENTO]
,B.COD_ID
,A.[COD_CAE]
,A.[PERC_CS_PUBLICO]
,A.[PERC_CS_PRIVADO]
,A.[PERC_CS_ESTRANGEIRO]
,A.[CAPITAL_SOCIAL]
,A.[TOTAL_HOMENS]
,A.[TOTAL_MULHERES]
,A.[VOL_NEGOCIOS]
,A.[TOTAL_ESTABELECIMENTOS]
,A.[NOME_TOC]
,A.[NIF_TOC]
,A.[MORADA_TOC]
,A.[OBS]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_EMPRESA A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ESCRITORIOS
SELECT
[NIF_ANTIGO]
,0
,[NOME]
,[MORADA]
,[LOCALIDADE]
,[COD_REP_FISCAL]
,[COD_PROVINCIA]
,[COD_MUNICIPIO]
,[ARRENDADO]
,
,[SENHORIO]
,[VALOR]
,[TELEFONE]
,[FAX]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_ESCRITORIOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.GERENTES
SELECT
[NIF_ANTIGO]
,[NOME]
,[MORADA]
,[NIF_GERENTE]
,[COD_GERENTE]
,GETDATE()
,''SGCT300''
,null
,null
FROM
dbo.BULK_GERENTES
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.MATRICULAS
SELECT
NIF_ANTIGO,
MATRICULA,
GETDATE(),
''SGCT300'',
null,
null
FROM
BULK_MATRICULAS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SINGULARES
SELECTA.[NIF_ANTIGO]
,A.[BI]
,A.[PENSIONISTA]
,A.[NIF_ENT_PATRONAL]
,NULL
,A.[COD_PROFISSAO]
,NULL
,A.[ACTIVID_DESDE]
,A.[LOCALIZACAO]
,A.[MORADA]
,A.[COD_PROVINCIA]
,A.[COD_MUNICIPIO]
,A.[TEM_EMPREGADOS]
,NULL
,A.[OUTRO_ESTAB]
,A.[PSERVTCO]
,A.[PSERVTCP]
,A.[PSERVA]
,A.[TIPO_EMP_CONTA_PROP_DATA]
,A.[TIPO_EMP_CONTA_PROP_OUTRA]
,A.[TIPO_EMP_CONTA_PROP_OUTRA_NIF]
,NULL
,A.[COD_TP_DOC_IDENTIFICACAO]
,A.[DTACESSACAOTCO]
,A.[DTACESSACAOTCP]
,A.[DTACESSACAOA]
,B.[COD_ID]
,A.[COD_CAE]
,A.[OLDNIF]
,A.[OBS]
,A.[MOTCESSTCP]
,A.[MOTCESSTCO]
,A.[MOTCESSAA]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_SINGULARES A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SOCIOS
SELECT [NIF_ANTIGO]
,[NOME]
,[NIF_SOCIO]
,[MORADA]
,NULL
,[COD_SOCIO]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_SOCIOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
To:
ALTERPROCEDURE [dbo].[spSGCT_CONTRIBUINTES_ALTERADOS] (@DB AS VARCHAR(50))
AS
DECLARE @STRSQL AS VARCHAR(8000)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ACT_SECUNDARIAS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ACTIVIDADES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.CONTRIB_IMPOSTOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.EMPRESA
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.ESCRITORIOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.GERENTES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.MATRICULAS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.SINGULARES
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'DELETE FROM '+@DB+'.dbo.SOCIOS
WHERE
NIF_ANTIGO IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SGCT300_CTB_RF_CHANGED
SELECT A.NIF_ANTIGO
FROM
(SELECT * FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+''')) A,
'+@DB+'.dbo.CONTRIBUINTES B
WHERE
A.NIF_ANTIGO = B.NIF_ANTIGO
AND
A.COD_REP_FISCAL <> B.COD_REP_FISCAL'
EXEC (@STRSQL)
SET @STRSQL = 'UPDATE '+@DB+'.[dbo].[CONTRIBUINTES]
SET [NIF] = A.NIF
,[FILIAL_NUMBER] = A.FILIAL_NUMBER
,[STATUS] = A.STATUS
,[COD_GRP_CONTRIBUINTE] = A.COD_GRP_CONTRIBUINTE
,[COD_MET_TRIBUTARIO] = A.COD_MET_TRIBUTARIO
,[COD_TP_SINGULARES] = A.COD_TP_SINGULARES
,[COD_TP_IMPOSTOS] = A.COD_TP_IMPOSTOS
,[GRANDE_CONTRIBUINTE] = A.GRANDE_CONTRIBUINTE
,[NOME] = A.NOME
,[MORADA] = A.MORADA
,[C_POSTAL] = A.C_POSTAL
,[TELEFONE] = A.TELEFONE
,[FAX] = A.FAX
,[COD_PROVINCIA] = A.COD_PROVINCIA
,[COD_MUNICIPIO] = A.COD_MUNICIPIO
,[COD_REP_FISCAL] = A.COD_REP_FISCAL
,[COD_COMUNA] = A.COD_COMUNA
,[DT_INICIO] = A.DT_INICIO
,[DT_ALTERACAO] = A.DT_ALTERACAO
,[DT_CESSACAO] = A.DT_CESSACAO
,[COD_MOT_CESSACAO] = A.COD_MOT_CESSACAO
,[MOT_CESSACAO] = A.MOT_CESSACAO
,[COD_TP_INSTITUICAO] = A.COD_TP_INSTITUICAO
,[COD_FUNC] = NULL
,[COD_BAIRRO] = A.COD_BAIRRO
,[COD_CIDADE] = A.COD_CIDADE
,[OBS] = A.OBS
,[DT_BEGIN] = A.DT_BEGIN
,[NUMEROPOSTO] = ''SGCT300''
FROM
(SELECT * FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+''')) A,
'+@DB+'.[dbo].[CONTRIBUINTES] b
WHERE
b.NIF_ANTIGO = A.NIF_ANTIGO'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ACT_SECUNDARIAS
SELECT A.NIF_ANTIGO, A.COD_CAE, B.COD_ID, GETDATE(),''SGCT300'',null,null
FROM
BULK_ACT_SECUNDARIAS A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ACTIVIDADES
SELECT A.NIF_ANTIGO,B.CODIGO,B.DESCRICAO,B.INDEX_POS,''SGCT300'',GETDATE(),null,null
FROM
BULK_ACTIVIDADES A,
'+@DB+'.dbo.TP_ACTIVIDADES B
WHERE
A.INDEX_POS = B.INDEX_POS
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.CONTRIB_IMPOSTOS
SELECT
NIF_ANTIGO,
COD_IMPOSTO,
GETDATE(),
''SGCTCENTRA'',
null,
null
FROM
BULK_CONTRIB_IMPOSTOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.EMPRESA
SELECT A.[NIF_ANTIGO]
,A.[DT_CONSTITUICAO]
,A.[DESIGNACAO_COMERCIAL]
,A.[NUM_DR]
,A.[DT_DR]
,A.[COD_ENQUADRAMENTO]
,B.COD_ID
,A.[COD_CAE]
,A.[PERC_CS_PUBLICO]
,A.[PERC_CS_PRIVADO]
,A.[PERC_CS_ESTRANGEIRO]
,A.[CAPITAL_SOCIAL]
,A.[TOTAL_HOMENS]
,A.[TOTAL_MULHERES]
,A.[VOL_NEGOCIOS]
,A.[TOTAL_ESTABELECIMENTOS]
,A.[NOME_TOC]
,A.[NIF_TOC]
,A.[MORADA_TOC]
,A.[OBS]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_EMPRESA A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.ESCRITORIOS
SELECT
[NIF_ANTIGO]
,0
,[NOME]
,[MORADA]
,[LOCALIDADE]
,[COD_REP_FISCAL]
,[COD_PROVINCIA]
,[COD_MUNICIPIO]
,[ARRENDADO]
,
,[SENHORIO]
,[VALOR]
,[TELEFONE]
,[FAX]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_ESCRITORIOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.GERENTES
SELECT
[NIF_ANTIGO]
,[NOME]
,[MORADA]
,[NIF_GERENTE]
,[COD_GERENTE]
,GETDATE()
,''SGCT300''
,null
,null
FROM
dbo.BULK_GERENTES
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.MATRICULAS
SELECT
NIF_ANTIGO,
MATRICULA,
GETDATE(),
''SGCT300'',
null,
null
FROM
BULK_MATRICULAS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SINGULARES
SELECTA.[NIF_ANTIGO]
,A.[BI]
,A.[PENSIONISTA]
,A.[NIF_ENT_PATRONAL]
,NULL
,A.[COD_PROFISSAO]
,NULL
,A.[ACTIVID_DESDE]
,A.[LOCALIZACAO]
,A.[MORADA]
,A.[COD_PROVINCIA]
,A.[COD_MUNICIPIO]
,A.[TEM_EMPREGADOS]
,NULL
,A.[OUTRO_ESTAB]
,A.[PSERVTCO]
,A.[PSERVTCP]
,A.[PSERVA]
,A.[TIPO_EMP_CONTA_PROP_DATA]
,A.[TIPO_EMP_CONTA_PROP_OUTRA]
,A.[TIPO_EMP_CONTA_PROP_OUTRA_NIF]
,NULL
,A.[COD_TP_DOC_IDENTIFICACAO]
,A.[DTACESSACAOTCO]
,A.[DTACESSACAOTCP]
,A.[DTACESSACAOA]
,B.[COD_ID]
,A.[COD_CAE]
,A.[OLDNIF]
,A.[OBS]
,A.[MOTCESSTCP]
,A.[MOTCESSTCO]
,A.[MOTCESSAA]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_SINGULARES A,
'+@DB+'.dbo.SECTORES B
WHERE
A.COD_SECTOR = B.CODIGO
AND
A.NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
SET @STRSQL = 'INSERT INTO '+@DB+'.dbo.SOCIOS
SELECT [NIF_ANTIGO]
,[NOME]
,[NIF_SOCIO]
,[MORADA]
,NULL
,[COD_SOCIO]
,GETDATE()
,''SGCT300''
,null
,null
FROM
BULK_SOCIOS
WHERE
NIF_ANTIGO
IN
(SELECT NIF_ANTIGO FROM DBO.fcSGCT_RETURN_ALTERADOS('''+@DB+'''))'
EXEC (@STRSQL)
If I do it like this, If the second stored procedure fails, will the first stored procedure code be executed against the database?
June 19, 2012 at 7:28 am
That would also work. The entire group will then be executed in a single transaction. There are a couple of considerations if taking this approach.
1) Depending on the speed you may encounter increased waits as locks will remain for the duration of executing the entire group. This may or may not be an issue depending on performance of your queries and traffic in the system.
2) If you have other segments of code that were counting on the transactions inside the individual procs, those transactions are no longer there. That means individual calls to the procs no longer have transactions. This may or may not be an issue depending on other factors in your system.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 7:30 am
Please try wrapping your code in the IFcode shortcut to decrease the amount of space it takes. Use the [ code="sql"] [/code] without the space in the first one to do this.
Jared
CE - Microsoft
June 19, 2012 at 1:58 pm
He wanted a way to nest his 3 Stored Procedures such that if one failed they all would be rolled back, that doen't require a 4th transaction level outside scope of the stored procedure. So you do not need to wrap the calling of the stored procedure in transaction.
June 19, 2012 at 2:06 pm
That means that I can do as I told, correct?
Create a "master" stored procedure which calls the other three like this:
Create MasterProc
as
Begin
begin try
Begin Tran
exec proc 1
exec proc 2
exec proc 3
Commit tran
end try
Begin cath
rollback
end catch
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply