Optimize SP - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Optimize SP

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2011-10-01 : 13:56:58

Hi

In a accounting apps i have a table:

Ano Conta vdb0 vdb1 vdb2 ...
2011 6 20
2011 61 20
2011 611 20
2011 6111 10
2011 6112 10

I use the folowing SP to update vdbxx columns , but i want to know if there any way faster . the SP when update 6111 must update 611,61 and 6 too

The table as as index ano,cod_conta,tp_conta( the columns in where condition)

ALTER PROCEDURE [dbo].[AcumulaGeral]
@ano Varchar(4),
@conta Varchar(20),
@campo varchar(10),
@valor money

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @nc_c int
DECLARE @vc_c varchar(20)

set @nc_c=len(rtrim(@conta))

if @campo='VDB0'

WHILE (1=1)
BEGIN
set @vc_c=substring(@conta,1,@nc_c)
if @nc_c >=1
UPDATE CCTPOC
set VDB0=VDB0 + @valor
where ano=@ano
and COD_CONTA=@vc_c
and TP_CONTA='G'

else
BREAK

set @nc_c= @nc_c - 1
END

if @campo='VDB1'

WHILE (1=1)
BEGIN
set @vc_c=substring(@conta,1,@nc_c)
if @nc_c >=1
UPDATE CCTPOC
set VDB1=VDB1 + @valor
where ano=@ano
and COD_CONTA=@vc_c
and TP_CONTA='G'

else
BREAK

set @nc_c= @nc_c - 1
END

Regards

Pedro faro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 14:05:13

sorry didnt get your requirement completely. would you mind giving table structure with data and then explaining what you want with sample output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

psfaro
Starting Member

49 Posts

Posted - 2011-10-01 : 15:40:44

Hi,

table struct.

CREATE TABLE [dbo].[CCTPOC](
[ano] [varchar](4) NOT NULL,
[COD_CONTA] [varchar](20) NOT NULL,
[TP_CONTA] [varchar](1) NOT NULL,
[VDB0] [decimal](15, 2) NULL,
[VCR0] [decimal](15, 2) NULL,
[VDB1] [decimal](15, 2) NULL,
[VCR1] [decimal](15, 2) NULL,
[VDB2] [decimal](15, 2) NULL,
[VCR2] [decimal](15, 2) NULL,
...

Sample Data:

ano COD_CONTA TP_CONTA VDB0 VCR0 VDB1 VCR1
2011 6 G 0.00 0.00 8867036.09 79754.05
2011 61 G 0.00 0.00 3542768.64 0.00
2011 612 G 0.00 0.00 0.00 0.00
2011 616 G 0.00 0.00 3542768.64 0.00
2011 6161 G 0.00 0.00 3171699.04 0.00
2011 61611 G 0.00 0.00 3110310.01 0.00
2011 61612 G 0.00 0.00 61389.03 0.00
2011 61619 G 0.00 0.00 0.00 0.00
2011 6162 G 0.00 0.00 351444.77 0.00
2011 6163 G 0.00 0.00 0.00 0.00

Regards

Pedro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 01:59:53

and what should be the output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

psfaro
Starting Member

49 Posts

Posted - 2011-10-02 : 11:47:01

Hi,

The SP receives 4 arguments , and must update the table .
There's no output.

The SP can update several records(columns vdbxx) at a time , depends of column "conta " sended.

example:

sended "2011','6112','vdb0',100

Must update records: 6112,611,61,6

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 13:49:10

[code]
ALTER PROCEDURE [dbo].[AcumulaGeral]
@ano Varchar(4),
@conta Varchar(20),
@campo varchar(10),
@valor money

AS
BEGIN
UPDATE CCTPOC
set VDB0=CASE WHEN @campo='VDB0' THEN VDB0 + @valor ELSE VDB0 END,
VDB1=CASE WHEN @campo='VDB1' THEN VDB1 + @valor ELSE VDB1 END,
....
where ano=@ano
and @conta LIKE CAST(COD_CONTA AS varchar(10))+ '%'
and TP_CONTA='G'
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

psfaro
Starting Member

49 Posts

Posted - 2011-10-02 : 20:43:35

Hi VisaKh.

Great !!

Tanks very much!

You're the man.

Regards
Pedro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:05:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources