Le niveau maximal d'imbrication

Le
PePiCK
Je me tappe cette erreur ce matin

Avec cette requête, tout fonctionne bien:
SELECT
s.iso,
firstdb.dbo.GetName(s.uID,'fr-CA') [state name]

FROM
firstdb.dbo.State s

Mais en ajoutant une table d'une base de données externe, j'obtiens
cette erreur:
Serveur : Msg 217, Niveau 16, État 1, Procédure GetName, Ligne 21
Le niveau maximal d'imbrication des procédures stockées, des
fonctions, des déclencheurs ou des vues est dépassé (limite 32).

SELECT
c.Name,
s.iso,
firstdb.dbo.GetName(s.uID,'fr-CA') [state name]

FROM
secondedb.dbo.Client c LEFT JOIN
firstdb.dbo.State s
ON (c.State_Province = s.iso)


Il y un moyen de faire fonctionner le tout ?




Script de test (firstdb seulement):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[GetName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetName]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[Name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Name]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[State]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[State]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[Country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Country]
GO



CREATE TABLE [dbo].[Country] (
[uID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[numcode] [smallint] NULL ,
[iso] [char] (2) COLLATE French_CI_AI NOT NULL ,
[iso3] [char] (3) COLLATE French_CI_AI NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Country] WITH NOCHECK ADD
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[uID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Country] WITH NOCHECK ADD
CONSTRAINT [DF_Country_uID] DEFAULT (newid()) FOR [uID],
CONSTRAINT [IX_country] UNIQUE NONCLUSTERED
(
[iso]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO Country(numcode,iso,iso3) VALUES (124,'CA','CAN')
GO
INSERT INTO Country(numcode,iso,iso3) VALUES (840,'US','USA')
GO


CREATE TABLE [dbo].[State] (
[uID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[iso] [char] (2) COLLATE French_CI_AI NOT NULL ,
[uID_Country] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] WITH NOCHECK ADD
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[uID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] WITH NOCHECK ADD
CONSTRAINT [DF_States_uID] DEFAULT (newid()) FOR [uID],
CONSTRAINT [IX_States] UNIQUE NONCLUSTERED
(
[iso]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] ADD
CONSTRAINT [FK_State_Country] FOREIGN KEY
(
[uID_Country]
) REFERENCES [dbo].[Country] (
[uID]
) ON UPDATE CASCADE
GO

DECLARE @uID_Country uniqueidentifier
SELECT @uID_Country=uID FROM Country WHERE iso='CA'
INSERT INTO State(iso, uID_Country) VALUES('QC',@uID_Country)
INSERT INTO State(iso, uID_Country) VALUES('BC',@uID_Country)
SELECT @uID_Country=uID FROM Country WHERE iso='US'
INSERT INTO State(iso, uID_Country) VALUES('SC',@uID_Country)
INSERT INTO State(iso, uID_Country) VALUES('ND',@uID_Country)
GO


CREATE TABLE [dbo].[Name] (
[uID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fk_uID] [uniqueidentifier] NOT NULL ,
[Culture] [nvarchar] (10) COLLATE French_CI_AI NOT NULL ,
[name] [varchar] (80) COLLATE French_CI_AI NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Name] WITH NOCHECK ADD
CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED
(
[uID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Name] WITH NOCHECK ADD
CONSTRAINT [DF_Name_uID] DEFAULT (newid()) FOR [uID],
CONSTRAINT [IX_Name] UNIQUE NONCLUSTERED
(
[fk_uID],
[Culture]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

DECLARE @fk_uID uniqueidentifier
SELECT @fk_uID=uID FROM State WHERE iso='QC'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-
CA','Quebec')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-
CA','Québec')
SELECT @fk_uID=uID FROM State WHERE iso='BC'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-
CA','British-Columbia')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-
CA','Colombie-Britanique')
SELECT @fk_uID=uID FROM State WHERE iso='SC'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-CA','South
Carolina')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-
CA','Caroline du Sud')
SELECT @fk_uID=uID FROM State WHERE iso='ND'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-CA','North
Dakota')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-CA','Dakota
du Nord')
GO

CREATE FUNCTION GetName(@fk_uID uniqueidentifier, @culture
nvarchar(10))
RETURNS nvarchar(80) AS
BEGIN

DECLARE @name nvarchar(80)
SELECT
@name=[Name]

FROM
Corporate.dbo.[Name]

WHERE
fk_uID=@fk_uID AND
culture LIKE @culture + '%'

IF @name is null
BEGIN
IF (CHARINDEX('-',REVERSE(@culture),0) = 0)
SET @name=Corporate.dbo.GetName(@fk_uID,'en')
ELSE
SET @name=Corporate.dbo.GetName(@fk_uID,SUBSTRING(@culture,
0,LEN(@culture)-CHARINDEX('-',REVERSE(@culture),0)+1))
END
RETURN @name
END
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Fred BROUARD
Le #17563101
MS SQL Server est limité volontairement à 32 appels récursif (pour
éviter de faire tomber le moteur par le fait de développeur
imprudents).. Or votre fonction est susceptible de s'appeler elle même
indéfiniment.

Conclusion : programmez votre fonction en itératif.... D'autant qu'une
UDF c'est déjà pas performant, alors en plus en récursif, c'est du vice !

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************


PePiCK a écrit :
Je me tappe cette erreur ce matin...

Avec cette requête, tout fonctionne bien:
SELECT
s.iso,
firstdb.dbo.GetName(s.uID,'fr-CA') [state name]

FROM
firstdb.dbo.State s

Mais en ajoutant une table d'une base de données externe, j'obtiens
cette erreur:
Serveur : Msg 217, Niveau 16, État 1, Procédure GetName, Ligne 21
Le niveau maximal d'imbrication des procédures stockées, des
fonctions, des déclencheurs ou des vues est dépassé (limite 32).

SELECT
c.Name,
s.iso,
firstdb.dbo.GetName(s.uID,'fr-CA') [state name]

FROM
secondedb.dbo.Client c LEFT JOIN
firstdb.dbo.State s
ON (c.State_Province = s.iso)


Il y un moyen de faire fonctionner le tout ?




Script de test (firstdb seulement):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[GetName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetName]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[Name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Name]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[State]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[State]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[Country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Country]
GO



CREATE TABLE [dbo].[Country] (
[uID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[numcode] [smallint] NULL ,
[iso] [char] (2) COLLATE French_CI_AI NOT NULL ,
[iso3] [char] (3) COLLATE French_CI_AI NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Country] WITH NOCHECK ADD
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[uID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Country] WITH NOCHECK ADD
CONSTRAINT [DF_Country_uID] DEFAULT (newid()) FOR [uID],
CONSTRAINT [IX_country] UNIQUE NONCLUSTERED
(
[iso]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO Country(numcode,iso,iso3) VALUES (124,'CA','CAN')
GO
INSERT INTO Country(numcode,iso,iso3) VALUES (840,'US','USA')
GO


CREATE TABLE [dbo].[State] (
[uID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[iso] [char] (2) COLLATE French_CI_AI NOT NULL ,
[uID_Country] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] WITH NOCHECK ADD
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[uID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] WITH NOCHECK ADD
CONSTRAINT [DF_States_uID] DEFAULT (newid()) FOR [uID],
CONSTRAINT [IX_States] UNIQUE NONCLUSTERED
(
[iso]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] ADD
CONSTRAINT [FK_State_Country] FOREIGN KEY
(
[uID_Country]
) REFERENCES [dbo].[Country] (
[uID]
) ON UPDATE CASCADE
GO

DECLARE @uID_Country uniqueidentifier
SELECT @uID_Country=uID FROM Country WHERE iso='CA'
INSERT INTO State(iso, uID_Country) VALUES('QC',@uID_Country)
INSERT INTO State(iso, uID_Country) VALUES('BC',@uID_Country)
SELECT @uID_Country=uID FROM Country WHERE iso='US'
INSERT INTO State(iso, uID_Country) VALUES('SC',@uID_Country)
INSERT INTO State(iso, uID_Country) VALUES('ND',@uID_Country)
GO


CREATE TABLE [dbo].[Name] (
[uID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fk_uID] [uniqueidentifier] NOT NULL ,
[Culture] [nvarchar] (10) COLLATE French_CI_AI NOT NULL ,
[name] [varchar] (80) COLLATE French_CI_AI NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Name] WITH NOCHECK ADD
CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED
(
[uID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Name] WITH NOCHECK ADD
CONSTRAINT [DF_Name_uID] DEFAULT (newid()) FOR [uID],
CONSTRAINT [IX_Name] UNIQUE NONCLUSTERED
(
[fk_uID],
[Culture]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

DECLARE @fk_uID uniqueidentifier
SELECT @fk_uID=uID FROM State WHERE iso='QC'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-
CA','Quebec')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-
CA','Québec')
SELECT @fk_uID=uID FROM State WHERE iso='BC'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-
CA','British-Columbia')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-
CA','Colombie-Britanique')
SELECT @fk_uID=uID FROM State WHERE iso='SC'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-CA','South
Carolina')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-
CA','Caroline du Sud')
SELECT @fk_uID=uID FROM State WHERE iso='ND'
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'en-CA','North
Dakota')
INSERT INTO [Name](fk_uID,Culture,Name) VALUES(@fk_uID,'fr-CA','Dakota
du Nord')
GO

CREATE FUNCTION GetName(@fk_uID uniqueidentifier, @culture
nvarchar(10))
RETURNS nvarchar(80) AS
BEGIN

DECLARE @name nvarchar(80)
SELECT
@name=[Name]

FROM
Corporate.dbo.[Name]

WHERE
fk_uID=@fk_uID AND
culture LIKE @culture + '%'

IF @name is null
BEGIN
IF (CHARINDEX('-',REVERSE(@culture),0) = 0)
SET @name=Corporate.dbo.GetName(@fk_uID,'en')
ELSE
SET @name=Corporate.dbo.GetName(@fk_uID,SUBSTRING(@culture,
0,LEN(@culture)-CHARINDEX('-',REVERSE(@culture),0)+1))
END
RETURN @name
END




PePiCK
Le #17562881
J'ai trouvé l'erreur, c'est presque genant d'en parler !

un @fk_uID null venait foutre en l'air ma récursivité...
Un simple test ajouté dans la function corrige le tout.

Désolé du dérengement ! ;)

PePiCK
Publicité
Poster une réponse
Anonyme