ZMzQNBja

1IF OBJECT_ID('dbo.CSCreateColumn') IS NOT NULL DROP PROCEDURE dbo.CSCreateColumn
2SET QUOTED_IDENTIFIER ON 
3GO
4CREATE PROC dbo.CSCreateColumn(@TableName char(30), @ColumnName varchar(60), @Parameter varchar(100), @Default varchar(30), @Description varchar(100), @TermID int, @DimType bigint, @Length int, @Dimensions int, @Scale int, @Flags int,@SectionName varchar(250) = '',@Reference varchar(250) = '') AS
5  DECLARE @SeqNr int
6  DECLARE @Counter int
7  DECLARE @DimColumnName varchar(60)
8  SET @Counter = 0
9  SET @DimColumnName = @ColumnName
10  IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = @TableName) BEGIN
11      EXEC('CREATE TABLE dbo.' + @TableName + '(' + @DimColumnName + ' ' + @Parameter + ' ' + @Default + ')')
12  END
13  WHILE @Counter < @Dimensions BEGIN
14      IF @Dimensions > 1 BEGIN
15          SET @DimColumnName = @ColumnName + '_' + CONVERT(char(2), @Counter)
16      END
17      IF NOT EXISTS (SELECT ID FROM syscolumns WHERE ID = OBJECT_ID(@TableName) AND name = @DimColumnName) BEGIN
18          EXEC('ALTER TABLE ' + @TableName + ' ADD ' + @DimColumnName + ' ' + @Parameter + ' ' + @Default + '')
19      END ELSE BEGIN
20          IF NOT @ColumnName = 'sysguid' AND NOT @Parameter = 'TEXT NULL' AND NOT @ColumnName = 'ID' AND NOT (@DimType & 255) = 44 BEGIN
21              EXEC('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @DimColumnName + ' ' + @Parameter + '')
22          END
23      END
24      SET @Counter = @Counter + 1
25  END
26  IF EXISTS (SELECT TableName FROM DDColumns WHERE TableName = @TableName) BEGIN
27      SELECT @SeqNr = MAX(SeqNr) + 1 FROM DDColumns WHERE TableName = @TableName
28  END ELSE BEGIN
29      SET @SeqNr = 0
30  END
31--DDType 51 is used for Front office text, when SDKDeveloper toolkit then also create DD, will be
32--deleted again in the CSSDKDevCustomSolutions.sql
33  IF (@DimType & 255) <> 51 OR (SELECT StringValue FROM BacoSettings WHERE SettingType = 0 AND SettingGroup = 'general' AND SettingName = 'GroupID') <> '' BEGIN 
34      IF NOT EXISTS (SELECT ID FROM DDColumns WHERE Tablename = @TableName AND ColumnName = @DimColumnName) BEGIN 
35          IF UPPER(@ColumnName) <> 'SYSGUID' OR (LEFT(UPPER(@TableName),2)= 'CS' OR LEFT(UPPER(@TableName),3) = 'SDK') BEGIN
36              IF @TermID IS NOT NULL SELECT @TermID = ID FROM TermsCustomized WHERE substring(CSTermID, len(CSTermID)-5, 6) = @TermID
37              INSERT INTO DDColumns (TableName, ColumnName,Description,TermID,Type,Length,Dimensions,Scale,Flags,SeqNr)
38              VALUES (@TableName,@ColumnName,@Description,@TermID,@DimType,@Length,@Dimensions,@Scale,@Flags,@SeqNr)
39          END
40      END
41  END
42GO
43SET QUOTED_IDENTIFIER OFF
44GO