Alter PROCEDURE spdBomPeggingUpdate
@q1 nvarchar(25)='%'
AS
DECLARE @sql varchar(2500)
SET NOCOUNT ON
SET @sql='Alter VIEW qryBomPegging AS '
SET @sql=@sql+'SELECT TOP 100 PERCENT BD.子編號,IM.品名,IM.規格,IM.單位,BD.ParentID '
SET @sql=@sql+' FROM dbo.tabBomDetail BD INNER JOIN '
SET @sql=@sql+' dbo.tabItemMaster IM ON '
SET @sql=@sql+' BD.子編號 = IM.編號 INNER JOIN '
SET @sql=@sql+' dbo.tabItemMaster IM1 ON BD.ParentID = IM1.編號 '
SET @sql=@sql+' WHERE IM1.展開 = 1 AND BD.子編號 LIKE ''%' [url=mailto:+@q1+'%''']+@q1+'%'''[/url]
SET @sql=@sql+' ORDER BY BD.子編號 '
SET NOCOUNT OFF
EXEC (@sql)
RETURN
如要生成交叉查詢表,可用如下存儲過程:
Alter PROCEDURE spdCostTotalUpdate
@inputtext varchar(100)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(2500)
DECLARE @temp1 nvarchar(20)
SELECT DISTINCT 材料分類
INTO #tabCostClassification_temp
FROM qryCostDetail
SELECT @sql='Alter Procedure spdCostTotal'
SELECT @sql=@sql+' As SELECT 說明,ParentID, 品名, 規格, 單位,修改日期,SUM(金額) AS 合計,'
WHILE (0=0)
BEGIN
IF EXISTS(SELECT * FROM #tabCostClassification_temp)
BEGIN
SELECT @temp1=材料分類
FROM #tabCostClassification_temp
-- ORDER BY 編號 DESC
SET @sql=@sql+' SUM(CASE 材料分類 WHEN ''' + @temp1 + ''' THEN 金額 ELSE NULL END) AS ' + @temp1 +' ,'
DELETE FROM #tabCostClassification_temp
WHERE 材料分類=@temp1
END
ELSE
BREAK
END
SET @sql=LEFT(@sql,LEN(@sql)-1)+' FROM dbo.qryCostDetail '
SET @sql=@sql+@inputtext
SET @sql=@sql+' GROUP BY 說明,ParentID, 品名, 規格, 單位,修改日期 '
SET @sql=@sql+' ORDER BY ParentID'
SET NOCOUNT OFF
EXEC (@sql)
RETURN