Avec cette requ=EAte, 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=E9es externe, j'obtiens
cette erreur:
Serveur : Msg 217, Niveau 16, =C9tat 1, Proc=E9dure GetName, Ligne 21
Le niveau maximal d'imbrication des proc=E9dures stock=E9es, des
fonctions, des d=E9clencheurs ou des vues est d=E9pass=E9 (limite 32).
FROM
secondedb.dbo.Client c LEFT JOIN
firstdb.dbo.State s
ON (c.State_Province =3D s.iso)
Il y un moyen de faire fonctionner le tout ?
Script de test (firstdb seulement):
if exists (select * from dbo.sysobjects where id =3D 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 =3D object_id(N'[dbo].
[Name]') and OBJECTPROPERTY(id, N'IsUserTable') =3D 1)
drop table [dbo].[Name]
GO
if exists (select * from dbo.sysobjects where id =3D object_id(N'[dbo].
[State]') and OBJECTPROPERTY(id, N'IsUserTable') =3D 1)
drop table [dbo].[State]
GO
if exists (select * from dbo.sysobjects where id =3D object_id(N'[dbo].
[Country]') and OBJECTPROPERTY(id, N'IsUserTable') =3D 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 =3D 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 =3D 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 =3D 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 =3D 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=3DuID FROM Country WHERE iso=3D'CA'
INSERT INTO State(iso, uID_Country) VALUES('QC',@uID_Country)
INSERT INTO State(iso, uID_Country) VALUES('BC',@uID_Country)
SELECT @uID_Country=3DuID FROM Country WHERE iso=3D'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 =3D 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 =3D 90 ON [PRIMARY]
GO
DECLARE @fk_uID uniqueidentifier
SELECT @fk_uID=3DuID FROM State WHERE iso=3D'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=E9bec')
SELECT @fk_uID=3DuID FROM State WHERE iso=3D'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=3DuID FROM State WHERE iso=3D'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=3DuID FROM State WHERE iso=3D'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=3D[Name]
FROM
Corporate.dbo.[Name]
WHERE
fk_uID=3D@fk_uID AND
culture LIKE @culture + '%'
IF @name is null
BEGIN
IF (CHARINDEX('-',REVERSE(@culture),0) =3D 0)
SET @name=3DCorporate.dbo.GetName(@fk_uID,'en')
ELSE
SET @name=3DCorporate.dbo.GetName(@fk_uID,SUBSTRING(@culture,
0,LEN(@culture)-CHARINDEX('-',REVERSE(@culture),0)+1))
END
RETURN @name
END
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Fred BROUARD
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).
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
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).
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
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).
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
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
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.