SQL Server Performance Issue

#1

I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.

I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.

It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??

If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Please help me understand this to solve this problem.

Thanks, AR

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCosto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT

IF @ccs_ids = 'null'
RETURN

SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@ccs_ids,',')

IF @cantidad = 1
BEGIN
SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
IF @cantidad = 0
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
RETURN
END
ELSE
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
a.ccs_id
FROM
dbo.fn_CentroCosto(@ccs_id) As a

FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
END
ELSE
BEGIN
DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@ccs_ids,',') AS a )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
ccs_id
FROM
dbo.fn_CentroCosto(@ccs_id)
WHERE
ccs_id not in (select ccs_id from
@Ret)

FETCH NEXT FROM ListadoCcs INTO @ccs_id
END

CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULL
PRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'
ELSE
PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'
go

#2

Firstly , could you confirm the 2 dbs are similar in terms of
indices,statistics and execution plans. For example, when your run the UDF
are they similar exceution plans?

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com

"Andres Rormoser" <arormoser (AT) gmail (DOT) com> wrote

Quote:

I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.

I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.

It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??

If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Please help me understand this to solve this problem.

Thanks, AR

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCosto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT

IF @ccs_ids = 'null'
RETURN

SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@ccs_ids,',')

IF @cantidad = 1
BEGIN
SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
IF @cantidad = 0
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
RETURN
END
ELSE
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
a.ccs_id
FROM
dbo.fn_CentroCosto(@ccs_id) As a

FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
END
ELSE
BEGIN
DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@ccs_ids,',') AS a )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
ccs_id
FROM
dbo.fn_CentroCosto(@ccs_id)
WHERE
ccs_id not in (select ccs_id from
@Ret)

FETCH NEXT FROM ListadoCcs INTO @ccs_id
END

CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULL
PRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'
ELSE
PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'
go

#3

Yes, the database are exactly the same (it's a restore from the
previous day) .

Exactly the same Execution Plans. I already update statics in the
database, also measure Memory/CPU/Virtual Memory/Disk I/O.

But can determine where the problem is. Now i'm installing a RAID 1 in
my development machine, to test if that is the problem, but i don't
think so.

On 15 feb, 16:54, "Jack Vamvas" <DEL_TO_RE... (AT) del (DOT) com> wrote:

Quote:

Firstly , could you confirm the 2 dbs are similar in terms of
indices,statistics and execution plans. For example, when your run the UDF
are they similar exceution plans?

--

Jack Vamvas
___________________________________
Search *IT jobs from multiple sources- *http://www.ITjobfeed.com

"Andres Rormoser" <arormo... (AT) gmail (DOT) com> wrote in message

news:6d10d455-0c58-4aec-a928-1cf3c31b0bc0 (AT) i7g2000prf (DOT) googlegroups.com...

I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.

I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.

It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??

If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Please help me understand this to solve this problem.

Thanks, AR

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCosto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
* *DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT

* *IF @ccs_ids = 'null'
* * * *RETURN

* *SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@ccs_ids,',')

* *IF @cantidad = 1
* *BEGIN
* * * *SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
* * * *IF @cantidad = 0
* * * *BEGIN
* * * * * *INSERT INTO @Ret SELECT @ccs_ids
* * * * * *RETURN
* * * *END
* * * *ELSE
* * * *BEGIN
* * * * * *INSERT INTO @Ret SELECT @ccs_ids
* * * * * *DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
* * * * * *OPEN ListadoCcs
* * * * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * * * *WHILE @@FETCH_STATUS = 0
* * * * * *BEGIN
* * * * * * * *INSERT INTO
* * * * * * * * * *@Ret
* * * * * * * *SELECT
* * * * * * * * * *a.ccs_id
* * * * * * * *FROM
* * * * * * * * * *dbo.fn_CentroCosto(@ccs_id) As a

* * * * * * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * * * *END
* * * * * *CLOSE ListadoCcs
* * * * * *DEALLOCATE ListadoCcs
* * * *END
* *END
* *ELSE
* *BEGIN
* * * *DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@ccs_ids,',') AS a )
* * * *OPEN ListadoCcs
* * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * *WHILE @@FETCH_STATUS = 0
* * * *BEGIN
* * * * * *INSERT INTO
* * * * * * * *@Ret
* * * * * *SELECT
* * * * * * * *ccs_id
* * * * * *FROM
* * * * * * * *dbo.fn_CentroCosto(@ccs_id)
* * * * * *WHERE
* * * * * * * *ccs_id not in (select ccs_id from
@Ret)

* * * * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * *END

* * * *CLOSE ListadoCcs
* * * *DEALLOCATE ListadoCcs
* *END
* *RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULL
* *PRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'
ELSE
* *PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'
go- Ocultar texto de la cita -

- Mostrar texto de la cita -

#4

Also we test other heavy queries and they work OK in both machines, it
seems that the problem is when the query use this specific UDF.

On 15 feb, 17:43, Andres Rormoser <arormo... (AT) gmail (DOT) com> wrote:

Quote:

Yes, the database are exactly the same (it's a restore from the
previous day) .

Exactly the same Execution Plans. I already update statics in the
database, also measure Memory/CPU/Virtual Memory/Disk I/O.

But can determine where the problem is. Now i'm installing a RAID 1 in
my development machine, to test if that is the problem, but i don't
think so.

On 15 feb, 16:54, "Jack Vamvas" <DEL_TO_RE... (AT) del (DOT) com> wrote:

Firstly , could you confirm the 2 dbs are similar in terms of
indices,statistics and execution plans. For example, when your run the UDF
are they similar exceution plans?

--

Jack Vamvas
___________________________________
Search *IT jobs from multiple sources- *http://www.ITjobfeed.com

"Andres Rormoser" <arormo... (AT) gmail (DOT) com> wrote in message

news:6d10d455-0c58-4aec-a928-1cf3c31b0bc0 (AT) i7g2000prf (DOT) googlegroups.com...

I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.

I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.

It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??

If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Please help me understand this to solve this problem.

Thanks, AR

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCosto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
* *DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT

* *IF @ccs_ids = 'null'
* * * *RETURN

* *SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@ccs_ids,',')

* *IF @cantidad = 1
* *BEGIN
* * * *SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
* * * *IF @cantidad = 0
* * * *BEGIN
* * * * * *INSERT INTO @Ret SELECT @ccs_ids
* * * * * *RETURN
* * * *END
* * * *ELSE
* * * *BEGIN
* * * * * *INSERT INTO @Ret SELECT @ccs_ids
* * * * * *DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
* * * * * *OPEN ListadoCcs
* * * * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * * * *WHILE @@FETCH_STATUS = 0
* * * * * *BEGIN
* * * * * * * *INSERT INTO
* * * * * * * * * *@Ret
* * * * * * * *SELECT
* * * * * * * * * *a.ccs_id
* * * * * * * *FROM
* * * * * * * * * *dbo.fn_CentroCosto(@ccs_id) As a

* * * * * * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * * * *END
* * * * * *CLOSE ListadoCcs
* * * * * *DEALLOCATE ListadoCcs
* * * *END
* *END
* *ELSE
* *BEGIN
* * * *DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@ccs_ids,',') AS a )
* * * *OPEN ListadoCcs
* * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * *WHILE @@FETCH_STATUS = 0
* * * *BEGIN
* * * * * *INSERT INTO
* * * * * * * *@Ret
* * * * * *SELECT
* * * * * * * *ccs_id
* * * * * *FROM
* * * * * * * *dbo.fn_CentroCosto(@ccs_id)
* * * * * *WHERE
* * * * * * * *ccs_id not in (select ccs_id from
@Ret)

* * * * * *FETCH NEXT FROM ListadoCcs INTO @ccs_id
* * * *END

* * * *CLOSE ListadoCcs
* * * *DEALLOCATE ListadoCcs
* *END
* *RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULL
* *PRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'
ELSE
* *PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'
go- Ocultar texto de la cita -

- Mostrar texto de la cita -- Ocultar texto de la cita -

- Mostrar texto de la cita -

#5

Andres Rormoser (arormoser (AT) gmail (DOT) com) writes:

Quote:

I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.
...
If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Neither 20 seconds nor two minutes appears to be acceptable performance.
I sense there is a need for a rewrite to improve performance. Before
I give it a stab, I would like to know:

1) Which version of SQL Server are you on?
2) Could you post the definition of the CentreCosto table including indexes?
3) What is the purpose of the function?
4) If you could post some sample data with the desired output, so that I
can test, that would be useful too.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#6

I'm using SQL Server 2000 8.00.2039.

I finnaly solve the problem!! The clasic restart! I restarted the SQL
Server and now it's working in 15s like it should.

Now, my question is why? It's a good practice to restart SQL server
every x days? Memory related issues? Is there any DBCC command to
"clean" memory?

Thanks!!!

On 15 feb, 20:44, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Andres Rormoser (arormo... (AT) gmail (DOT) com) writes:
I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.
...
If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Neither 20 seconds nor two minutes appears to be acceptable performance.
I sense there is a need for a rewrite to improve performance. Before
I give it a stab, I would like to know:

1) Which version of SQL Server are you on?
2) Could you post the definition of the CentreCosto table including indexes?
3) What is the purpose of the function?
4) If you could post some sample data with the desired output, so that I
* *can test, that would be useful too.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

#7

Andres Rormoser (arormoser (AT) gmail (DOT) com) writes:

Quote:

I'm using SQL Server 2000 8.00.2039.

I finnaly solve the problem!! The clasic restart! I restarted the SQL
Server and now it's working in 15s like it should.

No, that operation should run in far less than 15s if you coded
it properly. It's more difficult than on SQL 2005, but I'm still
prepare to take the challenge if I get the tables and data to play
with.

Quote:

Now, my question is why? It's a good practice to restart SQL server
every x days? Memory related issues? Is there any DBCC command to
"clean" memory?

No, it's definitely not a good idea to restart SQL Server on regular
basis. That is just a waste of time.

It sounds that you simply were the victim of a poor execution plan.
When SQL Server first biulds the execition for a function or a stored
procedure, it sniffs the input parameters and uses these values as
guidance. If the first call is with atypical parameters, this can lead
to a less good plan for the common case. In such case you could have
achieved the same result with sp_recompile on your function to flush
the plan for from the cache.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#8

Quote:

No, it's definitely not a good idea to restart SQL Server on regular
basis. That is just a waste of time.

It sounds that you simply were the victim of a poor execution plan.

This is the same conclusion I came to in the parallel thread in
microsoft.public.sqlserver.server.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Quote:

Andres Rormoser (arormoser (AT) gmail (DOT) com) writes:
I'm using SQL Server 2000 8.00.2039.

I finnaly solve the problem!! The clasic restart! I restarted the SQL
Server and now it's working in 15s like it should.

No, that operation should run in far less than 15s if you coded
it properly. It's more difficult than on SQL 2005, but I'm still
prepare to take the challenge if I get the tables and data to play
with.

Now, my question is why? It's a good practice to restart SQL server
every x days? Memory related issues? Is there any DBCC command to
"clean" memory?

No, it's definitely not a good idea to restart SQL Server on regular
basis. That is just a waste of time.

It sounds that you simply were the victim of a poor execution plan.
When SQL Server first biulds the execition for a function or a stored
procedure, it sniffs the input parameters and uses these values as
guidance. If the first call is with atypical parameters, this can lead
to a less good plan for the common case. In such case you could have
achieved the same result with sp_recompile on your function to flush
the plan for from the cache.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#9

Andres, please don't post the same question independently to multiple groups
as this causes duplication of effort. Instead, post the question once with
cross-post to other relevant groups.

There is additional information waiting for you in the
microsoft.public.sqlserver.server thread.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Andres Rormoser" <arormoser (AT) gmail (DOT) com> wrote

Quote:

I'm having a performance Issue with my Production SQL Server (2 x Xeon
Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the
querys take much longer than the querys in my development server . For
example a recursive UDF takes 20s in my development server and 2m in
my production server (both with same users load) but my production
server it's much hardware powerfull than the other server.

I start monitoring an realized that the Prod. Server consumes a lot of
Physical Disk Reads and Writes when i execute this example UDF query,
then I realized that the Prod. Server has a SATA RAID1 Disk Mirroring
and my Dev. Server do not has mirroring.

It seems that the RAID1 disk performance seems to be very important
when I execute this query, and my question is WHY??

If the query only reads one Table 'CentroCostos' witch has 1255
records, why DISK performance is so important? It should work with
this info on memory and not have to use so much disk i/o.

Please help me understand this to solve this problem.

Thanks, AR

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
CREATE FUNCTION dbo.fn_CentroCosto (@ccs_ids VARCHAR(4000))
RETURNS @Ret TABLE (ccs_id INT)
AS
BEGIN
DECLARE @ccs_ccs_id INT, @cantidad INT, @ccs_id INT

IF @ccs_ids = 'null'
RETURN

SELECT @cantidad = COUNT(*) FROM dbo.fn_split(@ccs_ids,',')

IF @cantidad = 1
BEGIN
SELECT @cantidad = COUNT(*) FROM CentroCosto WHERE ccs_ccs_id
= @ccs_ids
IF @cantidad = 0
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
RETURN
END
ELSE
BEGIN
INSERT INTO @Ret SELECT @ccs_ids
DECLARE ListadoCcs CURSOR FOR ( SELECT ccs_id FROM
CentroCosto WHERE ccs_ccs_id = @ccs_ids )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
a.ccs_id
FROM
dbo.fn_CentroCosto(@ccs_id) As a

FETCH NEXT FROM ListadoCcs INTO @ccs_id
END
CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
END
ELSE
BEGIN
DECLARE ListadoCcs CURSOR FOR ( SELECT a.Value FROM
dbo.fn_split(@ccs_ids,',') AS a )
OPEN ListadoCcs
FETCH NEXT FROM ListadoCcs INTO @ccs_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@Ret
SELECT
ccs_id
FROM
dbo.fn_CentroCosto(@ccs_id)
WHERE
ccs_id not in (select ccs_id from
@Ret)

FETCH NEXT FROM ListadoCcs INTO @ccs_id
END

CLOSE ListadoCcs
DEALLOCATE ListadoCcs
END
RETURN
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.fn_CentroCosto') IS NOT NULL
PRINT '<<< CREATED FUNCTION dbo.fn_CentroCosto >>>'
ELSE
PRINT '<<< FAILED CREATING FUNCTION dbo.fn_CentroCosto >>>'
go