aliasghar
چهارشنبه 11 آبان 1390, 08:03 صبح
سلام
فرض كنيد 4 جدول به ترتيب زير داشته باشيم
CREATE TABLE [Kala_Prop1] (
[KPr1_ID] [smallint] IDENTITY (1001, 1) NOT NULL ,
[KPr_Name] [nvarchar] (50) COLLATE Arabic_CI_AS NOT NULL ,
CONSTRAINT [PK_KPr1] PRIMARY KEY CLUSTERED
(
[KPr1_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_KPr1] UNIQUE NONCLUSTERED
(
[KPr_Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Kala_Prop2] (
[KPr2_ID] [smallint] IDENTITY (1001, 1) NOT NULL ,
[KPr_Name] [nvarchar] (50) COLLATE Arabic_CI_AS NOT NULL ,
[KPr_Desc] [nvarchar] (250) COLLATE Arabic_CI_AS NULL ,
CONSTRAINT [PK_KPr2] PRIMARY KEY CLUSTERED
(
[KPr2_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_KPr2] UNIQUE NONCLUSTERED
(
[KPr_Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Kala_Prop3] (
[KPr3_ID] [smallint] IDENTITY (1001, 1) NOT NULL ,
[KPr_Name] [nvarchar] (50) COLLATE Arabic_CI_AS NOT NULL ,
[KPr_Desc] [nvarchar] (250) COLLATE Arabic_CI_AS NULL ,
CONSTRAINT [PK_KPr3] PRIMARY KEY CLUSTERED
(
[KPr3_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_KPr3] UNIQUE NONCLUSTERED
(
[KPr_Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [KalaId] (
[K_Code] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[K_Name1] [nvarchar] (50) COLLATE Arabic_BIN NOT NULL ,
[K_Type] [char] (2) COLLATE Arabic_CI_AS NULL CONSTRAINT [DF_KalaId_K_Kind] DEFAULT ('Go'),
[K_PropID1] [smallint] NULL ,
[K_PropID2] [smallint] NULL ,
[K_PropID3] [smallint] NULL ,
CONSTRAINT [PK_KalaId] PRIMARY KEY CLUSTERED
(
[K_Code]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_KalaId] UNIQUE NONCLUSTERED
(
[K_Name1],
[K_Code]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_KalaId_Kala_Prop1] FOREIGN KEY
(
[K_PropID1]
) REFERENCES [Kala_Prop1] (
[KPr1_ID]
),
CONSTRAINT [FK_KalaId_Kala_Prop2] FOREIGN KEY
(
[K_PropID2]
) REFERENCES [Kala_Prop2] (
[KPr2_ID]
),
CONSTRAINT [FK_KalaId_Kala_Prop3] FOREIGN KEY
(
[K_PropID3]
) REFERENCES [Kala_Prop3] (
[KPr3_ID]
),
CONSTRAINT [CK_KalaId] CHECK ([K_Type] = 'GO' or ([K_Type] = 'SE' or ([K_Type] = 'PR' or [K_Type] = 'MA')))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
حالا مي خواهيم يك فانكشن ايجاد كنيم كه از جدول كالا نام كالا را به شكل زير نمايش دهد
K_Name1/KPr_Name1/KPr_Name2/KPr_Name3
البته اگر هر يك از لينكها مقدار نداشت آن را در نام كالا دخالت ندهد
ممنون
فرض كنيد 4 جدول به ترتيب زير داشته باشيم
CREATE TABLE [Kala_Prop1] (
[KPr1_ID] [smallint] IDENTITY (1001, 1) NOT NULL ,
[KPr_Name] [nvarchar] (50) COLLATE Arabic_CI_AS NOT NULL ,
CONSTRAINT [PK_KPr1] PRIMARY KEY CLUSTERED
(
[KPr1_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_KPr1] UNIQUE NONCLUSTERED
(
[KPr_Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Kala_Prop2] (
[KPr2_ID] [smallint] IDENTITY (1001, 1) NOT NULL ,
[KPr_Name] [nvarchar] (50) COLLATE Arabic_CI_AS NOT NULL ,
[KPr_Desc] [nvarchar] (250) COLLATE Arabic_CI_AS NULL ,
CONSTRAINT [PK_KPr2] PRIMARY KEY CLUSTERED
(
[KPr2_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_KPr2] UNIQUE NONCLUSTERED
(
[KPr_Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Kala_Prop3] (
[KPr3_ID] [smallint] IDENTITY (1001, 1) NOT NULL ,
[KPr_Name] [nvarchar] (50) COLLATE Arabic_CI_AS NOT NULL ,
[KPr_Desc] [nvarchar] (250) COLLATE Arabic_CI_AS NULL ,
CONSTRAINT [PK_KPr3] PRIMARY KEY CLUSTERED
(
[KPr3_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_KPr3] UNIQUE NONCLUSTERED
(
[KPr_Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [KalaId] (
[K_Code] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[K_Name1] [nvarchar] (50) COLLATE Arabic_BIN NOT NULL ,
[K_Type] [char] (2) COLLATE Arabic_CI_AS NULL CONSTRAINT [DF_KalaId_K_Kind] DEFAULT ('Go'),
[K_PropID1] [smallint] NULL ,
[K_PropID2] [smallint] NULL ,
[K_PropID3] [smallint] NULL ,
CONSTRAINT [PK_KalaId] PRIMARY KEY CLUSTERED
(
[K_Code]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_KalaId] UNIQUE NONCLUSTERED
(
[K_Name1],
[K_Code]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_KalaId_Kala_Prop1] FOREIGN KEY
(
[K_PropID1]
) REFERENCES [Kala_Prop1] (
[KPr1_ID]
),
CONSTRAINT [FK_KalaId_Kala_Prop2] FOREIGN KEY
(
[K_PropID2]
) REFERENCES [Kala_Prop2] (
[KPr2_ID]
),
CONSTRAINT [FK_KalaId_Kala_Prop3] FOREIGN KEY
(
[K_PropID3]
) REFERENCES [Kala_Prop3] (
[KPr3_ID]
),
CONSTRAINT [CK_KalaId] CHECK ([K_Type] = 'GO' or ([K_Type] = 'SE' or ([K_Type] = 'PR' or [K_Type] = 'MA')))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
حالا مي خواهيم يك فانكشن ايجاد كنيم كه از جدول كالا نام كالا را به شكل زير نمايش دهد
K_Name1/KPr_Name1/KPr_Name2/KPr_Name3
البته اگر هر يك از لينكها مقدار نداشت آن را در نام كالا دخالت ندهد
ممنون