[SQL]新增資料後取得自動新增的流水號

在MS SQL Server的SP中新增資料後

KEY如果為自動新增的流水號

可用下列方式取得

以MS SQL的範例資料庫Northwind為例

產品代碼為自動新增的流水號，新增一筆產品資料後取得產品代碼

可以使用下列三種方式取得

• 取得自動新增流水號方法

1. IDENT_CURRENT(@tableName)
• 不限工作範圍，指定的資料表(@tableName)的最後一個流水號
2. SCOPE_IDENTITY()
• 目前工作階段最後一次產生的流水號
3. @@IDENTITY
• 全域變數，取得整個資料庫最後一次產生的流水號

以Products與Categories為例，在同一個工作範圍中分別新增Products與Categories資料

然後用上面三種方法分別印出來

最後一筆為78

最後一筆為8

執行SQL如下:

INSERT [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
VALUES (N'Ina', 12, 2, N'12 boxes', 13.0000, 32, 0, 15, 0)

print SCOPE_IDENTITY(); --78(目前工作階段最後一次產生的流水號)

print IDENT_CURRENT('Products'); --78(Products資料表最後一個流水號)

INSERT [dbo].[Categories] ([CategoryName], [Description]) 
VALUES (N'Ina Car', N'Seaweed and fish')

print SCOPE_IDENTITY(); --9(目前工作階段最後一次產生的流水號)

print IDENT_CURRENT('Products'); --78(Products資料表最後一個流水號)

print IDENT_CURRENT('Categories'); --9(Categories資料表最後一個流水號)

print @@IDENTITY --9(取得整個資料庫最後一次產生的流水號)

執行結果

[SQL] 如何執行自己組合的SQL字串(sp_executesql)

寫sp的過程中

常常會遇到必須自己組合的sql

像是一直and下去的查詢條件

或是where的值是必須經過一段複雜的運算才出來的這種

之前的作法就是很直覺的把字串組合起來

像是

set @deleteSql = 'delete from aaa where projId = @paramPrjId and place not in ('+@strPlace+') ';
exec @deleteSql

其中in 裡面的資料就是上面說的where的值是必須經過一段複雜的運算才出來的這種

結果因為一串的分號跟逗號，造成字串打架

還以遇到數字型態也會出問題

他會一直說我沒有辦法把數字跟字串組合起來

(明明c#跟java就可以...)

遇到問題當然有請google大神

我想我一天8小時的工時大概有4小時在google吧...

原來有ms sql server有一個指令叫 sp_executesql

可以在後面加上n個參數，並且定義每個參數的型態

範例如下

set @deleteSql = 'delete from aaa where projId = @paramPrjId and place not in ('+@strPlace+') ';
 EXECUTE sp_executesql @deleteSql, N'@paramPrjId int',  
                        @paramPrjId = @prjId