Google Doc-Excel跨工作簿資料抓取

於Google Doc內的Excel想要讀取不同檔案的做表資料，
可以透過 ImportRange 函式，將資料轉入，

=ImportRange("參照工作簿key","資料範圍")

1.參照工作簿Key抓取方式如下圖，

僅複製key=後至#gid間字串即可，
2.於ImportRange第二個參數輸入要讀取的資料範圍即可！

範例：=ImportRange("0AplU-NopnRmHdFhlWW1WWnRzb01lYmxPMURkYl9RMnc","產品主檔!A:G")

參考自：Google 文件 說明

使用 STATISTICS 顯示SQL語法執行耗用相關資訊

STATISTICS IO-顯示執行硬碟IO相關資訊
STATISTICS TIME-顯示執行耗用時間相關資訊

SQLite 時間處理問題

在Delphi中透過ADO元件使用SQLite資料庫時，
抓取時間的資料格式如下

select datetime(CURRENT_TIMESTAMP,'localtime')
-------------------
2012-09-19 23:29:45

若將抓取的時間資料透過ADOQuery寫入，
會出現 is not a valid date and time 的錯誤訊息，
這是因為在台灣的時間格式 年、月、日 以"/"區隔而非"-"，
故會產生時間格式錯誤的訊息，
使用下列語法指定輸出的時間格式，即可避免此問題

select strftime('%Y/%m/%d %H:%M:%f',datetime(CURRENT_TIMESTAMP,'localtime'))
-------------------
2012/09/19 23:29:45

時間格式:
%d - 月份內的日期
%f - 秒數 (準確至千份一秒)
%H - 小時
%j - 年份內的第幾日 (沒有潤年最大 365, 潤年最大 366）
%m - 月份
%M - 分鐘
%s - Unix Time Stamp
%w - 星期 (0 是星期日，6 是星期六)
%W - 年份內的第幾個星期
%Y - 年份
%% - 顯示 % 時使用

參考自
Programming Design Notes-SQLite 日期和時間的操作

以COALESCE()取代ISNULL()

在德瑞克大的Blog看到一篇認識 COALESCE() 函數文章，
特別記錄一下，供以後自己參考！

一般我們使用ISNULL()會傳入兩個參數，
當第一個參數為NULL時，則會回傳第二個參數的值，
而COALESCE()不同於ISNULL()，在於可以傳入多個參數，
一直到第一個非NULL值的參數出現才回傳，
利用這個特性，也可以簡化當我們使用ISNULL()與CASE WHEN的搭配!!

使用 @@ROWCOUNT 抓取受影響資料筆數

MS SQL Server中提供@@ROWCOUNT函數，

可回傳上一個SQL語法影響的資料筆數，

利用此函數可以讓我們在編寫T-SQL語法時，

減少反覆利用select判斷資料次數，以增進效能！

SET NOCOUNT ON

DELETE FROM ExaRec
WHERE CorpNo='20001' AND PrgType='MATV16'

SELECT '刪除資料筆數-'+CONVERT(VARCHAR,@@ROWCOUNT)
----------------------
刪除資料筆數-4

參考自：

MSDN-@@ROWCOUNT (Transact-SQL)

IT狂想曲

使用 SET NOCOUNT 關閉受影響資料筆數訊息

當我們在撰寫T-SQL時，
如果遇到批次更新資料時，
伺服器會顯示資料異動筆數，

(XX個資料列受到影響)

也會和我們PRINT的訊息混在一起，
透過SET NOCOUNT ON，將回覆受影響資料筆數訊息關閉，
不僅可以減少訊息輸出，也可以提升查詢效能！

參考自：
MSDN-SET NOCOUNT (Transact-SQL)
德瑞克:SQL Server 學習筆記

flash player 11.3 crash 問題

最近flash player更新到11.3版後，使用Firefox常常會有當掉的情況，

於是移除後重新安裝舊版，但卻發生Firefox可以順利使用，

但是IE部分一直出現無法安裝的錯誤訊息，

最後利用反安裝軟體順利移除乾淨後，再重新安裝一次即可，

建議在bug尚未修正前，重新安裝時將flash player自動更新功能關閉。

Flash player 11.2 離線安裝版

網路資料整理術-Pocket

不同於使用diigo的資料整理方式，
我們常會在網路上看到一些有興趣、不錯的訊息，
但是當下並沒有太多的時間可以詳細的將整篇資料看完，
這時候我就會選擇以pocket這個網站提供的功能，來記錄網頁，
待時間允許時，再將這些為整理的資料消化掉！
重點是，它支援跨平台使用PC、iOS、Android，
這樣常在手持裝置看到不錯的文章，可記錄下來，等回到電腦上後慢慢觀看！

詳細的使用方式可參考
電腦玩物-Pocket Read it later 稍後閱讀同步、離線、免費行動口袋

網路資料整理術-diigo

常常在網路上看到不錯的網頁想要收藏管理(例如技術文件、保健資訊)，
建議各位可以使用diigo做紀錄，該網站可以儲存網址並記錄我們指定的標籤，
方便我們日後查詢這些網頁更方便，也不會將自己的書籤搞得很雜亂，
對我來說，網站加書簽，網頁資料加diigo

教學

我常用的Google工具-(1)

http://www.google.com.tw/insidesearch/stories/

看到這篇突然覺得，Google真的是深得我心，
除了搜尋功能，下面幾個功能也推薦各位使用看看，

Gmail-這當然就不用說啦，過濾垃圾郵件一把罩，
也可以將其他郵箱透過Gmail外部郵件接收功能，過濾郵件

閱讀器-沒有那麼多時間每個網站都去逛，
就可以將自己想要關注的網站透過RSS訂閱，蒐集新文章，節省瀏覽網站時間

快訊-可以將自己想要關注的的事情"關鍵字"建立，自動會將資料發送給你

搜尋透視-可針對關鍵字統計搜尋者的統計資料，對趨勢分析很有幫助

DELETE 使用 JOIN

DELETE FROM WIPPrdSpec
FROM WIPPrd P,WIPPrdSpec S
where S.ProdNo=P.ProdNo
and S.SetName='AntiSpecNo'
and P.Ptype='17010'

MS SQL 利用CTE查詢各群組前幾名

上一篇MS SQL 遞迴查詢，說明CTE的基本語法，
此篇跟遞查詢沒有關係，但利用CTE產生的暫存資料來篩選出各群組排名。

--資料以SpecNo分類並PnameD欄位資料長度做排名
WITH CTE_Spec AS (
    SELECT SpecNo,Token,PA,Pcolor,PnameD,RANK() OVER (PARTITION BY SpecNo ORDER BY LEN(PnameD)) AS Seq
    FROM Prod
    WHERE isNULL(SpecNo,'') <> ''
    GROUP BY SpecNo,Token,PA,Pcolor,PnameD
)

--抓取各群組第一名
SELECT SpecNo,PnameD
FROM CTE_Spec
WHERE Seq = 1

MS SQL 遞回查詢

在許多Table中會有資料自我參照的情況，
例如部門代號表，會有組織階層的關係，
若想要列出部門的階層關係，從MS SQL Server 2005開始，
便支援遞回查詢功能，可以很簡單的表示出我們要的結果！

/*------------------------------------------------------------------*/
--建立測試資料
if (select OBJECT_ID('tempdb..#Dep')) IS NOT NULL DROP TABLE #Dep
CREATE TABLE #Dep (DEPNo varchar(5),
                    DEPNa nvarchar(20),
                    FDEPNo varchar(5))

INSERT INTO #Dep Values ('0100','管理部',NULL)
INSERT INTO #Dep Values ('0200','財務部',NULL)
INSERT INTO #Dep Values ('0210','會計課','0200')
INSERT INTO #Dep Values ('0211','應收組','0210')
INSERT INTO #Dep Values ('0212','應付組','0210')
INSERT INTO #Dep Values ('0213','稅務組','0210')
INSERT INTO #Dep Values ('0214','國際會計組','0210')
INSERT INTO #Dep Values ('0220','財務課','0200')
INSERT INTO #Dep Values ('0221','出納組','0220')
INSERT INTO #Dep Values ('0222','銀行相關組','0220')

/*------------------------------------------------------------------*/
WITH Dep_CTE (FDEPNo,DEPNo,DEPNa,DEP_PATH,DEP_LEVEL) AS
(    
    --頂層
    select FDEPNo,DEPNo,DEPNa,CAST(DEPNa as varchar(MAX)),0 AS DT_LEVEL
    from #Dep
    where FDEPNo is NULL
    
    union all    
    
    --成員
    select D.FDEPNo,D.DEPNo,D.DEPNa,CAST(DC.DEP_PATH+' > '+D.DEPNa as varchar(MAX)),DC.DEP_LEVEL+1
    from #Dep D INNER JOIN Dep_CTE DC on D.FDEPNo=DC.DEPNo
)

select * from Dep_CTE
/*------------------------------------------------------------------*/

參考自
MSDN-使用一般資料表運算式的遞迴查詢
Dot Robin Notes-CTE遞回查詢-範例資料庫

MS SQL-UNPOVIT-橫向資料轉縱向

之前有寫過一篇PIVOT，可以將明細資料轉為二維匯總的橫向表示方式(如Excel資料)，
如果想要將橫向多欄位資料轉為縱向表示(明細)，則可使用UNPIVOT。

--範例
--建立暫存表格
DECLARE @Table TABLE
( ProdNo varchar(3),
 RepQty int,
 ReaQty int,
 RecQty int)

--產生測試資料 
DECLARE @i INT
SET @i = 1
WHILE @i <= 3
BEGIN
 INSERT INTO @Table values (
  REPLICATE('0',(3-LEN(CAST(@i as varchar(3)))))+CAST(@i as varchar(3)),
  @i*(@i+1),
  @i*(@i+3),
  @i*(@i+5))
 SET @i = @i + 1
END

--資料轉換
select ProdNo,QtyType,Qty
from @Table
UNPIVOT
 (
 Qty FOR QtyType IN (RepQty,ReaQty,RecQty)
 ) PV

原始資料

執行結果

參考自：~楓花雪岳~[SQL] PIVOT 和 UNPIVOT、MSDN-使用 PIVOT 和 UNPIVOT

MS SQL Function 回傳資料表(Table)

透過View可以將一些常用但又較為複雜的查詢語法，
產生虛擬的資料表，簡化我們的查詢，
但在View並無法利用T-SQL彙整更新我們想要得到的查詢結果，
此時可使用Function並將回傳型態設為Table，來達到需求！

--建立測試用Function

CREATE FUNCTION [dbo].[FN_TEST]
                (@xGPNo varchar(5))--傳入值
RETURNS @TABLE TABLE (--設定傳入值格式
                DTNo varchar(5),
                DTNa Nvarchar(50),
                Item Nvarchar(100))--設定回傳值格式
AS
BEGIN   
    --依據傳入GPNo將查詢結果INSERT至@TABLE
    INSERT INTO @TABLE (DTNo,DTNa)
    select DTNo,DTNa
    from NOM
    where GPNo=@xGPNo  

    --將@TABLE中DTNo及DTNa合併更新至Item欄位
    UPDATE @TABLE
    SET Item=DTNo+'-'+DTNa  

    RETURN--回傳
END

--查詢
select *
from FN_TEST('PI')

--結果
DTNo    DTNa    Item
MT    原材料    MT-原材料
PIP    在製品    PIP-在製品
WIP    中間製品    WIP-中間製品

XAMPP 安全性設定加強

XAMPP為方便開發人員測試，所以預設的安全性防護極低，
例如MySQL root未設定密碼，XAMPP管理頁面可直接進入...等，
建議安裝後修改設定，加強安全性防護！

1. MySQL root密碼設定
2. XAMPP管理頁面密碼及存取設定
3. phpmyadmin頁面密碼設定

第1、2點可由 http://localhost/security/xamppsecurity.php 進入設定，如下圖

詳細設定方式可參考下列網站
挨踢路人甲的-XAMPP的安全設定
Takol Living Here-XAMPP - 加強安全性

XAMPP VirtualHost 虛擬主機設定方式

如架設XAMPP完成，有需要使用到虛擬主機部分，
依照下列設定方式， 即可設定成功。

##於 C:WINDOWS\system32\drivers\etc\ 修改hosts檔
##新增
127.0.0.1    localhost
127.0.0.1    www.sample.com
127.0.0.1    help.sample.com

##於\xampp\apache\conf\extra\ 修改httpd-vhosts.conf檔
##新增
NameVirtualHost *:80

<VirtualHost *:80>
##指定www.sample.com路徑
    ServerAdmin postmaster@sample.com
    DocumentRoot "/xampp/htdocs/sample.com"
    ServerName www.sample.com
    ServerAlias www.sample.com
    ErrorLog "logs/sample.com-error.log"
    CustomLog "logs/sample.com-access.log" combined
</VirtualHost>

<VirtualHost *:80>
##指定help.sample.com路徑
    ServerAdmin postmaster@sample.com
    DocumentRoot "/xampp/htdocs/sample.com/help"
    ServerName help.sample.com
    ServerAlias help.sample.com
    ErrorLog "logs/sample.com-error.log"
    CustomLog "logs/sample.com-access.log" combined
</VirtualHost>

##避免localhost因上面設定也轉至 sample.com資料夾
<VirtualHost *:80>
    DocumentRoot "/xampp/htdocs/xampp"
    ServerName localhost
</VirtualHost>

主機端設定完畢後，還需將網域的DNS解析做修改，
以上面的例子為例需設定www、help指向到那個IP地址。

參考自 阿舍的 Drupal 架站經驗談-設定 XAMPP 的虛擬主機 ( Virtual Host)

MS SQL 利用Agent自動執行Stored Procedure

因作業上的需要，需要固定時間自動執行Stored Procedure做資料處理，
此時可利用MS SQL Server內的Agent進行排程作業(Express版不支援)，
1.SQL Server Agent--作業--新增作業
2.一般--名稱--設定作業名稱
3.步驟--新增
3-1.設定步驟名稱
3-2.類型--T-SQL
3-3.資料庫選擇
3-4.可直接在命令欄內輸入執行語法，或使用Exec執行Stored Procedure
4.排程--新增
4-1.設定排程時間
5.通知--可設定失敗時以郵件通知(Database Mail設定)

參考自 使用Sql Agent排程執行Stored Procedure

MS SQL Server Agent設定Database Mail

記錄一下設定Database Mail 較容易疏忽的地方，

於管理設定檔安全性頁面，需將新增設定的Mail Account

1."公用"-開啟

2."預設設定檔"-是

並且需在"操作員"處新增需要發送的帳號，

才可在其他設定"發送郵件通知"時，選擇寄送的收件者。

參考自

SQL Server 無敵手冊第十三篇-設定SQL Server的Database Mail

SQL Server 無敵手冊第十四篇-設定SQL Server Agent使用Database Mail

延伸記錄

如何清除 SQL Server 的 Database Mail 紀錄與發送失敗郵件

如何使用SQLServer的db mail配合procedure發html格式的信

MS SQL 利用Trigger紀錄Table異動資料

針對某些特定Table需要詳細記錄資料變更的歷史資訊，
可以利用Trigger及產生的inserted和deleted 臨時表格做紀錄！
作法可參考下列範例。

--建立測試表格
CREATE TABLE [dbo].[WIPPrd](
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL,
    [UsrCo] [varchar](5) NULL) ON [PRIMARY]

--建立記錄檔
CREATE TABLE [dbo].[WIPPrd_Log](
    [MState] [varchar] (10) NOT NULL,
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL,
    [UsrCo] [varchar](5) NULL )    
--建立TRIGGER-在WIPPrd表格更新、新增、刪除後觸發
CREATE TRIGGER dbo.TR_WIPPrd_Modify on dbo.WIPPrd AFTER UPDATE,INSERT,DELETE
AS
BEGIN   
    --表格異動資料時會產生暫存的inserted和deleted兩個表格
    --兩個表格格式資訊皆與原表格相同
    --inserted紀錄insert資料、update後資料
    --deleted紀錄delete資料、update前資料
   
    --依據異動方式將異動資料新增到記錄檔
    --inserted和deleted皆有資料表示為-UPDATE
    IF EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)
    BEGIN       
        insert into WIPPrd_Log select 'DELETE',* from deleted
        insert into WIPPrd_Log select 'INSERT',* from inserted
    END
    --inserted有資料deleted無資料表示為-INSERT
    ELSE IF EXISTS (select 1 from inserted) and Not EXISTS (select 1 from deleted)
        insert into WIPPrd_Log select 'INSERT',* from inserted
    --inserted無資料deleted有資料表示為-DELETE
    ELSE IF NOT EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)
        insert into WIPPrd_Log select 'DELETE',* from deleted

END
--測試
--清空資料 
delete from WIPPrd 
delete from WIPPrd_log
--新增資料
insert into WIPPrd values ('11','TEST_11','11',GETDATE(),'Dean','10001')
insert into WIPPrd values ('22','TEST_22','22',GETDATE(),'Dean','10001')
--更新資料
update WIPPrd set ProdNo='33',PnameD='TEST_33' where ProdNo='22'
--刪除資料
delete from WIPPrd
--查詢記錄檔
select * from WIPPrd_log
------------------------------------------------------------
INSERT    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001
INSERT    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001
INSERT    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001

參考自
~楓花雪岳~[SQL] Trigger - inserted & deleted Table
MSDN-使用 inserted 或 deleted 資料表
CSDN-Trigger中如何獲得更新前的數據

---------------------------------------------------------------------------
延伸閱讀-[MS SQL]-XML資料型態

MS SQL 查詢所有資料庫表格名稱

利用INFORMATION_SCHEMA查詢所有TABLE NAME

SELECT *
FROM INFORMATION_SCHEMA.TABLES 

參考自 MSDN-INFORMATION_SCHEMA

Firefox portable、Chrome 暫存檔設至Ramdisk

Firefox
1.網址輸入 about:config
2.篩選條件 browser.cache.disk.parent_directory
   數值 X:\TEMP(Ramdisk位置)
3.篩選條件 browser.cache.disk.capacity
   數值 512000 (暫存空間大小)

若篩選條件不存在，請自行新增！

參考自 明天的昨天
------------------------------------------------------

Chrome
1.建立執行檔捷徑
2.捷徑上右鍵內容-->分頁"捷徑"
3."目標(T)"欄位內，最後一個"後面空一格
4.填上--disk-cache-dir=X:\TEMP(Ramdisk位置)

參考自 [GC] 如何將Chrome的快取移動到Ramdisk

MS SQL與SQLite語法使用差異

1.Top N

MS SQL  select Top 5 * from Table
SQLite  select * from Table limit 0,5

2.GETDATE()

MS SQL  select GETDATE()
SQLite  select datetime(CURRENT_TIMESTAMP,'localtime')

3.SELECT INTO

MS SQL select * into Table2 from Table
SQLite CREATE TABLE table2 AS  select * from Table

SQLite 相關網站備忘

1.SQLite官網 SQLite
2.ODBC驅動 SQLite ODBC Driver
3.語法文件 SQL As Understood By SQLite
4.DB免費管理工具 SQLiteStudio

Delphi 中SQL Decimal資料型態小數位數進位問題

利用ADOQuery抓取資料，做數值運算時，
若SQL資料庫的資料型態為decimal(18,2)--2表示精準至小數2位，
小數進位方式為五捨六入，並非我們一般使用的四捨五入，
不知是否算為Delphi bug !

如35*1.777= 62.195
Delphi運算結果為62.19
正確應為62.20

var xDSubtot : Double
xDSubtot :=  Query_D.FieldByName('Qty').AsFloat * Query_D.FieldByName('UnitP').AsFloat;
Query_D.FieldByName('SubTot').AsVariant := FloatToStrF(xDSubtot,ffFixed,18,2);

Delphi 7連線SQLite資料庫

最近在研究於Delphi7中利用ODBC連線SQLite DB檔案，
將實做成功方法做個記錄。

先安裝SQLite ODBC Driver

//**在ADO內的ConnectionString直接帶入，不用連線精靈
procedure _SetSQLiteDBConnection;
var OD : TOpenDialog;
    ADOConn : TADOConnection;
    xDatabase : String;
begin
  OD := TOpenDialog.Create(NIL);

  OD.FileName := 'x:\mymoney';//**預設資料庫路徑
  OD.DefaultExt := '*.db';//**預設存檔格式
  OD.Filter := 'DB files (*.db)|*.DB';//**過濾顯示的檔案格式
  if Not OD.Execute then
  begin
    showmessage('選擇資料庫檔案失敗！');
    Abort;
  end;

  xDatabase := ';Database='+OD.FileName;
  ADOConn := TADOConnection.Create(NIL);
  ADOConn.LoginPrompt := False;
  ADOConn.ConnectionString := 'Driver={SQLite3 ODBC Driver}'+xDatabase;

  while Not ADOConn.Connected do
    ADOConn.Open;
end;

參考自 网语飘飘.Net/Delphi攻坚战

MS SQL Stored Procedure 初心者筆記

1.使用Exec執行動態SQL語法，若語法內有產生暫存Table，在執行結束後一併消失，故可以先在外面CREATE暫存表，再於EXEC內使用INSERT INTO

IF (select OBJECT_ID('tempdb..#TTmp')) IS NOT NULL DROP TABLE #TTmp
CREATE TABLE #TTmp (ProdNo varchar(16))
EXEC('INSERT INTO #TTmp select ProdNo from Prod')

2.WHERE IN篩選值不可以變數存放，如@Tmp = '1,2,3'，查詢資料集合不為1,2,3之結果 (解決方法)

T-SQL WHERE IN 變數 有多筆值查無資料錯誤

在撰寫Stored Procedure時，發現在where條件中若以宣告的變數篩選資料，
當變數為多筆值並搭配WHERE IN的時候會發生查詢不到資料的情況

DECLARE @xTmp VarCHAR(100)

SET @xTmp = 'A01,B01,B99'

select GPNa,DTNo,DTNa
from NOM
where GPNo='SO'
and DTNo in (@xTmp)

GPNa       DTNo           DTNa
---------- -------------- ----------------

(0 個資料列受到影響)

此時需自行新增一個切割字串放入暫存Table的Function來解決此問題

USE [DATABASENAME]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_SplitStr]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FN_SplitStr]

USE [DATABASENAME]
GO

CREATE FUNCTION [dbo].[FN_SplitStr](@xStr NVarchar(MAX),@xFStr varchar(1))
                RETURNS @TmpTable TABLE ( TmpStr NVarchar(MAX))
AS
BEGIN
    --@xStr 欲切割字串
    --@xFStr 切割用判定字元
    --@TmpTable 存放切割後資料

    DECLARE @xTmpStr NVarchar(MAX)--暫存字串

    SET @xStr = @xStr + @xFStr--分割字串最後加上一個分割字元
    SET @xTmpStr = ''--清空暫存字串
    WHILE CHARINDEX(@xFStr,@xStr)>0--判斷是否有分割字元存在
    BEGIN
        --SET @xTmpStr = @xTmpStr + SUBSTRING(@xStr,1,CHARINDEX(@xFStr,@xStr)-1)
        SET @xTmpStr = SUBSTRING(@xStr,1,CHARINDEX(@xFStr,@xStr)-1)
        IF Replace(@xTmpStr,' ','') <> ''--不為空值才寫入@TmpTable
            INSERT INTO @TmpTable (TmpStr) values (@xTmpStr)        SET @xStr = SUBSTRING(@xStr,CHARINDEX(@xFStr,@xStr)+1,LEN(@xStr))
    END

    RETURN
END

DECLARE @xTmp VarCHAR(100)
SET @xTmp = 'A01,B01,B99'

select GPNa,DTNo,DTNa
from NOM
where GPNo='SO'
and DTNo in (select * from dbo.FN_SplitStr(@xTmp,','))

GPNa       DTNo           DTNa
---------- -------------- ----------------
成品出入庫類別    A01     生產入庫
成品出入庫類別    B01     銷貨出庫
成品出入庫類別    B99     其他出庫

(3 個資料列受到影響)

參考自
[TSQL]Procdure 或 SqlParameter 來下SQL指令 Where In 多個值的變數解決方法
在T-SQL進行字串分割

[分享]記帳小管家-免傳輸線更新PC與Android帳本資料

首先還是要感謝慕雲大提供帳務小管家這麼好用的軟體，
電腦版的記帳作了兩年多了，昨天開始連Android也一併授權了，
在這邊提供自己目前使用的配套同步方式，可以省略傳輸線的動作！

必要條件：
1.這麼讚的軟體，請先贊助
2.申請Dropbox帳號並於電腦端安裝程式
   電腦玩物-Dropbox 雲端資料同步軟體能用來幹嘛？10個我最常被滿足需求
3.手機安裝 Dropsync-設定手機自動同步資料夾

想法：
利用Dropsync來自動同步dropbox內的檔案(目前Dropbox官方Android版程式無此功能)，
等於在每個有安裝dropbox的Client都有一份Android帳務的資料庫檔案，
我們在PC帳務小管家就可以很方便的指定我們剛剛設定存放資料庫的資料夾，
進行同步，而不用使用到傳輸線，同步完成後，當手機Dropsync下次自動執行同步時，
就會將同步後的資料庫檔案更新到手機內，這樣是不是方便很多呢？

Win7 取消工作列最近開啟程式檔案清單

在Win7中，在工作列程式會顯示最近開啟過的檔案列表，
若不想要此功能的話，可透過下列步驟關閉。
1.於工作列上滑鼠右鍵-->內容
2.切換至 [開始]功能表
3.將 儲存最近開啟的項目並顯示於[開始]功能表和工作列 取消

MS SQL-字串函數-CHARINDEX

利用CHARINDEX函數，可抓取字串中指定字串的起始位置，
以方便程式進行字串的相關處理動作。

CHARINDEX('BCD','abcde')--不分大小寫
-----------
2

CHARINDEX('BCD','abcde' COLLATE Latin1_General_CS_AS)--分大小寫
-----------
0

參考自：MSDN-CHARINDEX

MS SQL 字串函數-QUOTENAME-字串加上雙引號

利用MS SQL的QUOTENAME函數，
可以方便的將字串以引號包起來，
避免因為一堆引號降低程式碼可讀性，
及降低出錯機會。

select QUOTENAME('String','''')
--------------------------------------------------------- 
'String'

(1 個資料列受到影響)

參考自：QUOTENAME

T-SQL 特殊字碼表

Enter ---char(13)
換行-----char(10)
Tab------char(9)
單引號--char(39)
雙引號--char(34)

MS SQL 建立暫存表格 temp table

在MS SQL中建立暫存表方法有兩種，
1.在tempdb中建立
   I.區域-前置詞#-其他連線不可查詢此TABLE
   II.全域-前置詞##-其他連線可查詢此TABLE
   連線中斷後被清除

Create Table #NOM_tmp (
    GPNo varchar(3),                        
    DTNo Nvarchar(10),
    GPNa Nvarchar(10),
    DTNa Nvarchar(20))

2.在記憶體宣告建立
   批次作業結束後清除

DECLARE @NOM_tmp TABLE (
    GPNo varchar(3),                        
    DTNo Nvarchar(10),
    GPNa Nvarchar(10),
    DTNa Nvarchar(20))

參考自
MSDN-CREATE TABLE
建立#TempTable與Declare @TempTable有何差別

MS SQL Procedure 多個回傳值

上一篇 MS SQL 資料處理時多個回傳值 ，
再延伸到編寫Stored Procedure時，遇到需要多個回傳值的處理方式！

--範例如下
CREATE PROCEDURE [dbo].[SP_TEST]
    (@xGPNo varchar(2),@xDTNo varchar(3),@xRGPNa Nvarchar(20) OUTPUT,@xRDTNa Nvarchar(50) OUTPUT)--設定傳入值格式
AS
BEGIN
    DECLARE @xSQL Nvarchar(MAX)--存放動態語法
    DECLARE @xGPNa Nvarchar(20)--接收回傳值
    DECLARE @xDTNa Nvarchar(50)--接收回傳值
    DECLARE @xStatemen nvarChar(MAX)--sp_executesql 參數

    --參數設定順序需與語法一致
    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
    SET @xRGPNa=@xGPNa
    SET @xRDTNa=@xDTNa
END

--執行
DECLARE @xRGPNa Nvarchar(20)
DECLARE @xRDTNa Nvarchar(50)
EXEC dbo.SP_TEST 'SO','B01',@xRGPNa OUTPUT,@xRDTNa OUTPUT
select @xRGPNa+'-'+@xRDTNa
-----------------------------------------------------------------------
成品出入庫類別-銷貨出庫

(1 個資料列受到影響)

參考自：
MSDN-CREATE PROCEDURE
walter 心得筆記-[SQL Server] 如何接收 Store Procedure 的傳回值

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