MS SQL 資料處理時多個回傳值

在撰寫Stored Procedure時，若需要在一個查詢將多個資料欄位值存入各別的變數中，
可透過 sp_executesql 來達到需求

--範例
DECLARE @xGPNo varchar(2)--查詢變數
DECLARE @xDTNo varchar(3)--查詢變數
DECLARE @xSQL Nvarchar(MAX)--存放動態語法
DECLARE @xGPNa Nvarchar(20)--接收回傳值
DECLARE @xDTNa Nvarchar(50)--接收回傳值

DECLARE @xStatemen nvarChar(MAX)--sp_executesql 參數

SET @xGPNo = 'SO'--指定值
SET @xDTNo = 'B01'--指定值

--參數設定順序需與語法一致
SET @xStatemen = '@xGPNa Nvarchar(20) OUT,@xDTNa Nvarchar(50) OUT,@xGPNo varchar(2),@xDTNo varchar(3)'

--查詢語法
SET @xSQL = 'select @xGPNa=GPNa,@xDTNa=DTNa '+
            ' from NOM '+
            ' where GPNo='+Quotename(@xGPNo,'''')+
            ' and DTNo='+Quotename(@xDTNo,'''')

--語法、參數、回傳值及相關變數
EXEC sp_executesql @xSQL,@xStatemen,@xGPNa OUT,@xDTNa OUT,@xGPNo,@xDTNo

--結果   
select @xGPNa+'-'+@xDTNa
-----------------------------------------------------------------------
成品出入庫類別-銷貨出庫

(1 個資料列受到影響)

參考自：TechNet-sp_executesql、MSDN-使用 sp_executesql

MS SQL CURSOR 使用

當遇到在SQL中需逐筆處理抓取資料時，
可使用CURSOR來處理。

--範例如下
DECLARE Cursor_tmp CURSOR FOR --建立Cursor
        (select MixRubNo,MachNo,Ver,TMNo
        from #RubSpec_Tmp
        where GSeq='1')

OPEN Cursor_tmp--開啟Cursor
FETCH NEXT FROM Cursor_tmp INTO @xMixRubNo,@xMachNo,@xVer,@xTMNo--將值放入變數
WHILE @@FETCH_STATUS = 0--有回傳值
BEGIN
    UPDATE RubSpec SET isCal='1' where MixRubNo=@xMixRubNo
                    and MachNo=@xMachNo
                    and Ver=@xVer
                    and TmNo=@xTMNo

    FETCH NEXT FROM Cursor_tmp INTO @xMixRubNo,@xMachNo,@xVer,@xTMNo--將值放入變數
END       
CLOSE Cursor_tmp--關閉Cursor
DEALLOCATE Cursor_tmp--釋放Cursor

參考自：
MSDN-CURSOR
MSDN-@@FETCH_STATUS
MSDN-DEALLOCATE
日月-[SQL] Cursor 使用方法
好風工作室-[T-SQL]在巢狀迴圈抓@@fetch_status會回傳-1的解決方法

MS SQL 判斷資料表是否存在

--方法一-透過INFORMATION_SCHEMA.TABLES
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
and TABLE_NAME='RubSpec'

--方法二-透過sys.tables
select *
from sys.tables
where name='RubSpec'

--方法三-使用OBJECT_ID
select OBJECT_ID('RubSpec')
----若為暫存表格，因存放於tempdb，語法改寫為
select OBJECT_ID('tempdb..RubSpec')

--如果表格存在要一併刪除，可使用下述語法
IF (select OBJECT_ID('RubSpec')) IS NOT NULL Drop Table RubSpec

SQL資料列(分組)流水編號

若要將抓取的資料自動編上流水編號，
可以利用ROW_NUMBER 函式，達到需求！

ROW_NUMBER() OVER (partition by field order by field)

--流水編
select SN=ROW_NUMBER() OVER(order by DTNo),GPNa,DTNo,DTNa
from NOM
where GPNo ='BDT'
----------
1    外胎不良原因    01    含唇
2    外胎不良原因    02    軟唇
3    外胎不良原因    03    胎唇裂痕

--分組流水編
select SN=ROW_NUMBER() OVER(partition by GPNa order by DTNo),GPNa,DTNo,DTNa
from NOM
where GPNo in ('BDT','BID')
----------
1    外胎不良原因    01    含唇
2    外胎不良原因    02    軟唇
3    外胎不良原因    03    胎唇裂痕
1    內胎不良原因    01    嘴邊漏風
2    內胎不良原因    02    嘴邊薄料
3    內胎不良原因    03    嘴邊裂痕

參考自：MSDN-ROW_NUMBER

SQL自動補0

某些欄位依照資料流水編號紀錄，
如果預設的資料型態為數值型態，
則以1、2、3的形式紀錄，
若希望以001、002、003的方式表達，

可利用REPLICATE函數指定重複字串次數的功能達到需求，
REPLICATE(欲重複字串,欲重複次數)

--若為數字型態欄位需先轉為字串型態
select REPLICATE('0',(3-LEN(欄位)))+欄位
from TableName

select REPLICATE('0',(4-LEN(CONVERT(varchar(3),1))))+
        CONVERT(varchar(3),1)
-------------------------------------------------
001

select REPLICATE('0', (4-LEN(CONVERT(Varchar(4),ROW_NUMBER()
          OVER(Order by M.ProdNo)))))+
          CONVERT(Varchar(4),ROW_NUMBER() OVER(Order by M.ProdNo)),M.ProdNo
from MATCost_tmp M
-------------------------------------------------
0001    R11010002
0002    R11010003
0003    R11020001

參考自：MSDN-REPLICATE 、The Will Will Web

QuickReport 群組頁次

在Delphi彙整資料以QuickReport輸出時，
針對頁次與總頁數可以很簡單的列印上去，
在使用GroupBand時若要依據群組顯示頁次，如
1/1
------
1/2
2/2
------
可使用下面的方式來達到需求！

//**宣告全域變數
private
    xPage : Integer;
    xIONo : String;
    xRecIONo : String;

//**在PageHeader的BeforePrint寫入計算規則
procedure TQR.PageHeaderBandBeforePrint(Sender: TQRCustomBand;
  var PrintBand: Boolean);
var xPos,xLen : Integer;
      xStr : String;
begin
  //**Query_Item 為QR的DataSet
  if xIONo <> Query_Item.fieldbyname('IONo').AsString then
  begin
    //**"單號-總頁數" 兩群組記錄間以","分開
    if xIONo <> '' then
      xRecIONo := xRecIONo+','+xIONo+'-'+IntToStr(xPage);

    xPage := 0;//**頁次歸0
    xIONo := Query_Item.fieldbyname('IONo').AsString;
  end;

  INC(xPage);//**累加頁次
  QRL_SubPage.Caption := IntToStr(xPage);//**群組頁次

  if AnsiContainsStr(xRecIONo,xIONo) then//**產生preview前不需判斷
  begin
    //**"單號-總頁數"
    xStr := xRecIONo;
    xPos := Pos(xIONo,xStr);
    xLen := Length(xStr);

    //**拆單號-群祖頁數
    xStr := Copy(xStr,xPos+1,xLen-xPos);
    xPos := Pos(',',xStr);
    xStr := Copy(xStr,0,xPos-1);

    //**拆頁數
    xPos := Pos('-',xStr);
    xLen := Length(xStr);
    xStr := Copy(xStr,xPos+1,xLen);

    QRL_GroupPage.Caption := xStr;//**群組頁數
  end;
end;

//**在DataSet的EndOfRecordset寫入資料行結尾處理
procedure TQR.Query_ItemEndOfRecordset(DataSet: TCustomADODataSet;
  var MoreData: WordBool; var EventStatus: TEventStatus);
begin
  //**
  //**"單號-總頁數" 兩群組記錄間以","分開
  if xIONo <> '' then
    xRecIONo := xRecIONo+','+xIONo+'-'+IntToStr(xPage);

  xPage := 0;//**頁次歸0
  xIONo := '';
end;

Delphi 7 在 Win7 無法使用 help

因Win7預設無法開啟.hlp的檔案，
故在Delphi中要開啟help檔案查詢資料會出現錯誤的行況，
只要安裝官方WinHlp32.exe的更新檔即可！
適用於 Windows 7 的 Windows 說明程式 (WinHlp32.exe)

控制 Hint 顯示時間

在Delphi中若想要讓原件的Hint顯示久一點的話，
可以透過參數設定修改！

Application.HintHidePause := 5000;//**顯示時間 單位為毫秒，即5秒

其他參數
Application.HintPause//**延遲時間
Application.HintColor//**顏色

以上可在Form Create時設定即可！

Win7下使用Delphi7

當在Win7下面安裝完Delphi執行時會出現檔案無法異動的錯誤訊息，
這是因為資料夾檔案權限問題，修改下述目錄權限即可！

1.修改 C:\Program Files\Borland\Delphi7\Bin 目錄權限，安全性中新增users有modify權限
2.修改 C:\Program Files\Borland\Delphi7\Projects 目錄權限，安全性中新增users有modify權限

Delphi利用ADOQuery直接讀取Excel內容

在ADOQuery的ConnectionString設定
Provider=Microsoft.Jet.OLEDB.4.0;Data Source='檔案路徑';
Extended Properties=Excel 8.0;Persist Security Info=False';

SQL輸入 select * from [Sheet1$]
<分頁名稱一定要用[]包起來>

ADOQuery Open後Excel的第一欄會變成FieldName，
下面的開始才是明細資料

使用完畢記得將ADOQuery Free掉，避免Excel檔案被鎖定無法正常開啟