none
為什麼StoreProcedure在SSMS裡的執行結果,和在應用程式中呼叫出來的結果不一樣? RRS feed

  • 問題

  • 我有一個Store Procedure,有三個輸入參數(@unit nvarchar(2), @year int, @month int),是用來計算BY每年、每月、每單位的一些數據。輸入不同的單位,SELECT出來的欄位數量也不一樣。

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[GetEQOPABFormByUnit] 
    	-- Add the parameters for the stored procedure here
    	@unit nvarchar(2), @year int, @month int
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
      -- Insert statements for procedure here
    
    -- 宣告「權重值總和」變數
    DECLARE @FactorSUM decimal(6,2)
    
    --=====================================================================================================
    -- 宣告一個暫存資料表
    -- 把每個月的當月份資料放到tmpTable1
    DECLARE @tmpTable1 table (
    ......................................);
    
    INSERT INTO @tmpTable1
    	
    	SELECT --..................省略
    --====================================================================================
    -- 宣告一個暫存資料表
    -- 把每個月的當月份累計平均資料放到tmpTable2
    DECLARE @tmpTable2 table (
    [......................................);
    
    INSERT INTO @tmpTable2
    	SELECT --..................省略
    
    END
    --====================================================================================
    --====================================================================================
    --====================================================================================
    
    --根據輸入的「單位」變數不同,來決定執行SELECT
    --====================================================================================
    
    	IF @unit = 'W1'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'W1' AND [Year] = @year
    
    	SELECT ' ' AS [項目]
    		,'權重' AS [分類]
    		,SUM(CASE WHEN [ProductionLineID] = 1 THEN [ABFactor] END) AS [一號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 2 THEN [ABFactor] END) AS [二號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 3 THEN [ABFactor] END) AS [三號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 4 THEN [ABFactor] END) AS [四號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 1 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 2 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 3 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 4 THEN [ABFactor] END) AS [合計]
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '機械故障次數' AS [項目]
    		,'本月份' AS [分類]
    		,SUM(CASE WHEN [ProductionLineID] = 1 THEN [MFLTM] END) AS [一號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 2 THEN [MFLTM] END) AS [二號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 3 THEN [MFLTM] END) AS [三號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 4 THEN [MFLTM] END) AS [四號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 1 THEN [MFLTM] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 2 THEN [MFLTM] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 3 THEN [MFLTM] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 4 THEN [MFLTM] END) AS [合計]
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '機械故障次數' AS [項目]
    		,'累計平均' AS [分類]
    FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '機械故障小時' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '機械故障小時' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '儀電故障次數' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '儀電故障次數' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '儀電故障小時' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '儀電故障小時' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '程控故障次數' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '程控故障次數' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '程控故障小時' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '程控故障小時' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '合計故障次數' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '合計故障次數' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '合計故障小時' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '合計故障小時' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '設備月曆定修率' AS [項目]
    		,'本月份' AS [分類]
    		,SUM(CASE WHEN [ProductionLineID] = 1 THEN [EQSDR] END)*100 AS [一號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 2 THEN [EQSDR] END)*100 AS [二號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 3 THEN [EQSDR] END)*100 AS [三號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 4 THEN [EQSDR] END)*100 AS [四號高爐線]
    		,SUM(CASE WHEN [ProductionLineID] = 1 THEN [EQSDR] * [ABFactor] / @FactorSUM END)*100 
    		+SUM(CASE WHEN [ProductionLineID] = 2 THEN [EQSDR] * [ABFactor] / @FactorSUM END)*100
    		+SUM(CASE WHEN [ProductionLineID] = 3 THEN [EQSDR] * [ABFactor] / @FactorSUM END)*100
    		+SUM(CASE WHEN [ProductionLineID] = 4 THEN [EQSDR] * [ABFactor] / @FactorSUM END)*100 AS [合計]
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '設備月曆定修率' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '設備月曆故障率' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '設備月曆故障率' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
     UNION ALL
    
    	SELECT '設備月曆可用率' AS [項目]
    		,'本月份' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable1
    
     UNION ALL
    
    	SELECT '設備月曆可用率' AS [項目]
    		,'累計平均' AS [分類]
    --....................省略,結構同上
    	FROM @tmpTable2
    
    	END --(OF IF @unit = 'W1')
    --========================================================================================================
    --========================================================================================================
    --========================================================================================================
    	ELSE IF @unit = 'W2'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'W2' AND [Year] = @year
    
    	SELECT ' ' AS [項目]
    		,'權重' AS [分類]
    		,SUM(CASE WHEN [ProductionLineID] = 5 THEN [ABFactor] END) AS [W22燒結工場#1SP]
    		,SUM(CASE WHEN [ProductionLineID] = 6 THEN [ABFactor] END) AS [W22燒結工場#2SP]
    		,SUM(CASE WHEN [ProductionLineID] = 7 THEN [ABFactor] END) AS [W22燒結工場#3SP]
    		,SUM(CASE WHEN [ProductionLineID] = 8 THEN [ABFactor] END) AS [W22燒結工場#4SP]
    		,SUM(CASE WHEN [ProductionLineID] = 9 THEN [ABFactor] END) AS [W23高爐一場#1BF]
    		,SUM(CASE WHEN [ProductionLineID] = 10 THEN [ABFactor] END) AS [W23高爐一場#2BF]
    		,SUM(CASE WHEN [ProductionLineID] = 11 THEN [ABFactor] END) AS [W24高爐二場#3BF]
    		,SUM(CASE WHEN [ProductionLineID] = 12 THEN [ABFactor] END) AS [W24高爐二場#4BF]
    		,SUM(CASE WHEN [ProductionLineID] = 5 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 6 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 7 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 8 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 9 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 10 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 11 THEN [ABFactor] END) 
    		+SUM(CASE WHEN [ProductionLineID] = 12 THEN [ABFactor] END) AS [合計]
    	FROM @tmpTable1
    
     UNION ALL
    
    --底下省略,同W1,只是ProductionLineID換成W2的產線
    
    	END --(OF IF @unit = 'W2')
    --========================================================================================================
    --========================================================================================================
    --========================================================================================================
    
    	ELSE IF @unit = 'W3'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'W3' AND [Year] = @year
    
    --底下省略,同W1,只是ProductionLineID換成W3的產線
    
    	END --(OF IF @unit = 'W3')
    --========================================================================================================
    --========================================================================================================
    --========================================================================================================
    
    	ELSE IF @unit = 'W4'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'W4' AND [Year] = @year
    
    
    
    --底下省略,同W1,只是ProductionLineID換成W4的產線
    
    	END --(OF IF @unit = 'W4')
    --===================================================================================================
    --========================================================================================================
    --========================================================================================================
    
    	ELSE IF @unit = 'Y4'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'Y4' AND [Year] = @year
    
    	SELECT ' ' AS [項目]
    		,'權重' AS [分類]
    
    --底下省略,同W1,只是ProductionLineID換成Y4的產線
    
    	END --(OF IF @unit = 'Y4')
    --========================================================================================================
    --========================================================================================================
    --========================================================================================================
    
    	ELSE IF @unit = 'Y5'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'Y5' AND [Year] = @year
    
    	SELECT ' ' AS [項目]
    		,'權重' AS [分類]
    		--底下省略,同W1,只是ProductionLineID換成Y5的產線
    
    	END --(OF IF @unit = 'Y5')
    --========================================================================================================
    --========================================================================================================
    --========================================================================================================
    
    	ELSE IF @unit = 'W5'
    		BEGIN
    
    	SELECT @FactorSUM = SUM(ISNULL([ABFactor],1)) FROM [ProductionLineFactor] INNER JOIN [ProductionLine]
    	ON [ProductionLine].[ProductionLineID] = [ProductionLineFactor].[ProductionLineID]
    	WHERE [ProductionLineUnit]= 'W5' AND [Year] = @year
    
    	SELECT ' ' AS [項目]
    		,'權重' AS [分類]
    		
    --底下省略,同W1,只是ProductionLineID換成W5的產線
    
    	END --(OF IF @unit = 'W5')
    --========================================================================================================
    
    
    
    
    

    因為整個SP有將近4000行,不過大部分是重複的結構,只是根據 IF @unit='什麼' ,來執行不同區段的SQL。所以這邊我只貼了一小段上來,但好像還是太多,請見諒。

    很奇怪的是,在Management Studio裡面執行,會根據不同的「單位」,執行的結果,欄位也都不一樣。這是我想要的結果,執行是正確的。

    但是我把這個SP拿來應用程式裡面呼叫,出來的結果,不管輸入的單位為何,通通都只會出現第一個IF的區塊執行的結果,也就是「W1」的部分,百思不解。

    這是我的CODE:

    public List<GetEQOPABFormByUnit結果> GetABTable(string unit, int year, int month)
    	{
    		//DataTable dt = new DataTable();
    		OEEDataClassesDataContext OEE = new OEEDataClassesDataContext();
    		var result = (from s1 in OEE.GetEQOPABFormByUnit(unit, year, month) select s1).ToList();
    
    		OEE.Dispose();
    
    		return result;
    	}
    

    然後,在頁面用GridView去Bind它。

    我也試過把List To DataTable,也是一樣不管參數輸入為何,都只會出現Unit='W1'的查詢結果~~。嗚嗚

    請教各位高手解惑,感激不盡!!

    2010年7月30日 上午 03:20

所有回覆

  • 使用 SQL Profiler  看一下實際執行的參數為何?
    2010年7月30日 上午 03:33
  • 你有沒有先用GetCommand Method 或是 SQL Profiler 來確認一下

    傳入的 執行的參數確實是正確的?

    2010年7月30日 上午 03:34
  • 我用的是SSMS Express版,再加上第一次知道可以用這個工具來追蹤@@(拍謝,太嫩了我),所以上網找到一篇:http://blog.miniasp.com/post/2008/04/18/Profiler-for-Microsoft-SQL-Server-2005-Express-Edition.aspx

    下載了http://sites.google.com/site/sqlprofiler/ 這個東西回來看。

    但是建好了以後,卻找不到資料庫有執行我的SP耶?,能否順便告訴小弟這個工具怎麼使用啊?

    PS. 我是參考上面保哥那篇,勾選 RPCCompleted 與 SQLBatchCompleted 事件的 TextData 。

    2010年7月30日 上午 05:07
  • OEE.GetEQOPABFormByUnit(unit, year, month) 這是自己寫的資料存取物件嗎?...若是的話有沒有去追看看裡面的 unit 變數是否被動過手腳?


    Hunterpo's IT Vision - http://www.dotblogs.com.tw/hunterpo/
    2010年7月30日 上午 07:41
  • 是的,GetEQOPABFormByUnit這就是我說的那個Store Procedure。

    不好意思,再請教一下,您說的「追看看」,是怎麼做呀?要怎樣看看有沒被動過手腳?

    2010年7月30日 上午 11:39
  • 在使用 SQL Server 時的除錯,我們常常需要使用 SQL Profiler,來了解實際的 sql server 執行過程與效能。

    請見 http://msdn.microsoft.com/zh-tw/library/ms187929.aspx

    • 已標示為解答 Lolota Lee 2010年8月5日 上午 03:32
    • 已取消標示為解答 Lolota Lee 2010年8月5日 上午 03:32
    • 已提議為解答 Lolota Lee 2010年8月5日 上午 03:32
    • 已標示為解答 Lolota Lee 2010年8月6日 上午 08:28
    • 已取消標示為解答 gigipan 2010年8月6日 下午 03:39
    • 已取消提議為解答 gigipan 2010年8月6日 下午 03:53
    2010年7月31日 上午 02:54
  • sorry我把他取消標示了。

    這個問題依舊沒有得到解答,因為我實在沒有辦法從各位提供的提示裡面得到些什麼(太笨了我)。

    但是幾乎在問這個問題的同時,我就放棄了。由於時程不允許繼續追根究柢耗下去,因此我採用了另一種作法。

    我的sp並沒有改寫什麼,但是在ap裡面改採ado.net的寫法,也就是打開連線、呼叫sp、execreader、fill到datatable,這樣是OK的。

    只是我依舊不知道,為何採用LINQ的寫法就會出問題,那看起來好像是它先幫我執行好sp中第一個if的區塊,所以出現的都一直是第一段if的屬性。這樣看起來,也許我對linq似懂非懂,連語法可能都錯了吧我猜~

     

    但是,還是謝謝各位高手的幫忙囉!

    2010年8月6日 下午 03:53