Public feed
#600926
DROP PROCEDURE GetSpecMatrixTemplateGOCREATE PROCEDURE GetSpecMatrixTemplateAS DECLARE @TemplateCode varchar(32), @TestColumnCount int, @DataColumnCount int BEGIN SET @TemplateCode = 'B31.3' -- Check to see the requested code actually exists If Exists( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) BEGIN -- Check to see if there are actually any test columns SET @TestColumnCount = ( SELECT Count(SpecificationTestColumnIdLayout) FROM SpecificationTestColumnLayout STCL WHERE STCL.TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) ) IF @TestColumnCount > 0 BEGIN -- Go Get The Columns CREATE TABLE #TestColumnTable ( Id int not null identity, TestColumnIdLayout int, WeldTestTypeId int ) INSERT INTO #TestColumnTable (TestColumnIdLayout, WeldTestTypeId) SELECT SpecificationTestColumnIdLayout, WeldTestTypeId FROM SpecificationTestColumnLayout WHERE TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) END -- END IF -- Check to see if there are actually any data columns SET @DataColumnCount = ( SELECT Count(SpecificationMatrixDataColumnIdLayout) FROM SpecificationMatrixDataColumnLayout STCL WHERE STCL.TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) ) IF @DataColumnCount > 0 BEGIN -- Go Get The Columns CREATE TABLE #DataColumnTable ( Id int not null identity, DataColumnIdLayout int, ColumnName varchar(50) ) INSERT INTO #DataColumnTable (DataColumnIdLayout, ColumnName) SELECT SpecificationMatrixDataColumnIdLayout, NameLayout FROM SpecificationMatrixDataColumnLayout WHERE TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) END -- END IF IF @DataColumnCount > 0 OR @TestColumnCount > 0 BEGIN CREATE TABLE #MasterColumnHeadings ( ColumnId int not null identity, ColumnIdLayout int not null, ColumnName varchar(50), ColumnType char(4) ) INSERT INTO #MasterColumnHeadings (ColumnIdLayout, ColumnName, ColumnType) SELECT DataColumnIdLayout, ColumnName, 'DATA' FROM #DataColumnTable INSERT INTO #MasterColumnHeadings (ColumnIdLayout, ColumnName, ColumnType) SELECT TCT.TestColumnIdLayout, WTT.Abbreviation, 'TEST' FROM #TestColumnTable TCT, WeldTestType WTT WHERE TCT.WeldTestTypeId = WTT.WeldTestTypeId SELECT * FROM #MasterColumnHeadings END -- END IF END -- END IF ENDGO