Create PROCEDURE dbo.StoredProcedure1
/*(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)*/
AS
/* SET NOCOUNT ON */
DECLARE @PrefixName nvarchar(10)
DECLARE @LNGCODE nvarchar(10)
DECLARE @TBLName nvarchar(30)
DECLARE @RecommendedSrcTBLName nvarchar(60)
DECLARE @MetadataTBLName nvarchar(60)
DECLARE @sql nvarchar(600)
DECLARE @RecordCount int
— 1. Check DB_Version table exists or not.If exist, then
IF EXISTS( SELECT * FROM sys.tables WHERE NAME=‘DB_Version’)
BEGIN
— a. Check and update DB_Version table
IF NOT EXISTS( SELECT * FROM DB_VERSION WHERE VERSION_NUMBER =‘6.0.0.1’)
INSERT INTO DB_VERSION VALUES(‘6.0.0.1’,‘1 July 2008’,”)
IF NOT EXISTS( SELECT * FROM DB_VERSION WHERE VERSION_NUMBER =‘6.0.0.2’)
INSERT INTO DB_VERSION VALUES(‘6.0.0.2’,‘6 Oct 2008‘,‘Introduced Database Metadata Table’)
IF NOT EXISTS( SELECT * FROM DB_VERSION WHERE VERSION_NUMBER =‘6.0.0.3’)
INSERT INTO DB_VERSION VALUES(‘6.0.0.3’,’27 May 2009′,‘Introduced Recommended sources Table & added IC_IUS_Order column in UT_Data table’)
IF NOT EXISTS( SELECT * FROM DB_VERSION WHERE VERSION_NUMBER =‘6.0.0.4’)
INSERT INTO DB_VERSION VALUES(‘6.0.0.4’,’27 May 2009′,‘Data type of Data_NId column under Recommended sources Table changed to Long Integer and logic has been added to delete empty records from UT_Recommended_sources table’)
— b. Get all DB_Prefix from DB_Available_Databases
DECLARE PrefixCursor CURSOR FOR
Select AVLDB_PREFIX FROM DB_AVAILABLE_DATABASES
— Open cursor
OPEN PrefixCursor
FETCH NEXT FROM PrefixCursor INTO @PrefixName
IF @@FETCH_STATUS <> 0
PRINT ‘ <<None>>’
WHILE @@FETCH_STATUS = 0
BEGIN
— c. update AT_Data table
SET @TBLName= @PrefixName + ‘_DATA’
SET @sql=‘ALTER TABLE ‘+ @TBLName +‘ ADD INDICATOR_NID int ‘
EXEC (@sql)
SET @sql=‘ALTER TABLE ‘+ @TBLName +‘ ADD Unit_NID int ‘
EXEC (@sql)
SET @sql=‘ALTER TABLE ‘+ @TBLName +‘ ADD Subgroup_Val_NID int ‘
EXEC (@sql)
SET @sql=‘ALTER TABLE ‘+ @TBLName +‘ ADD IC_IUS_Order int ‘
EXEC (@sql)
— insert Recommended source table in each langauge
SET @TBLName= @PrefixName +‘_Language’
DECLARE @LngCursor CURSOR
SET @sql= ‘SET @LngCursor= CURSOR FORWARD_ONLY STATIC FOR SELECT Language_Code from ‘+ @TBLName +‘; OPEN @LngCursor’
EXEC sp_executesql
@sql,
N‘@LngCursor CURSOR OUTPUT’,
@LngCursor OUTPUT
— open cursor
FETCH NEXT FROM @LngCursor INTO @LNGCODE
WHILE @@FETCH_STATUS =0
BEGIN
— d. Create AT_RecommendedSources_en table
SET @RecommendedSrcTBLName= @PrefixName+ ‘_RecommendedSources_’ + @LNGCODE
SET @sql=‘CREATE TABLE ‘+ @RecommendedSrcTBLName +‘ ( RSRC_NId int Identity(1,1) primary key,
Data_NId int, IC_IUS_Label varchar(255) )’
EXEC (@sql)
— e. Create AT_DBMetadata_en
SET @MetadataTBLName= @PrefixName+ ‘_DBMetadata_’ + @LNGCODE
SET @sql=‘CREATE TABLE ‘+ @MetadataTBLName +‘ ( DBMtd_NId int Identity(1,1) primary key,
DBMtd_Desc ntext,
DBMtd_PubName nvarchar(50),
DBMtd_PubDate datetime,
DBMtd_PubCountry nvarchar(50),
DBMtd_PubRegion nvarchar(50),
DBMtd_PubOffice nvarchar(50),
DBMtd_AreaCnt decimal(18, 0),
DBMtd_IndCnt decimal(18, 0),
DBMtd_IUSCnt decimal(18, 0),
DBMtd_TimeCnt decimal(18, 0),
DBMtd_SrcCnt decimal(18, 0),
DBMtd_DataCnt decimal(18, 0) )’
EXEC (@sql)
— Get the next language.
FETCH NEXT FROM @LngCursor INTO @LNGCODE
END
— CLOSE AND DEALLOCATE CURSOR
CLOSE @LngCursor
DEALLOCATE @LngCursor
— Get the next PREFIX.
FETCH NEXT FROM PrefixCursor INTO @PrefixName
END
— Close and deallocate cusror
CLOSE PrefixCursor
DEALLOCATE PrefixCursor
END
else
/* 2. Show message “Not Valid DB” */
print ‘Not Valid Database’
return 1