利用這個作法的話，最近一筆會出現在最上面，順序會反過來
/*取得使用者的查詢結果*/
DECLARE @ChooseSYSID TABLE (SYS_ID INT);
INSERT INTO @ChooseSYSID(SYS_ID)
SELECT SYS_ID FROM dbo.NETBAR
WHERE (@INFORM_ID = ‘ ‘ OR INFORM_ID LIKE @INFORM_ID)
ORDER BY SYS_ID DESC
/*產生ROWID*/
DECLARE @RowNumber TABLE(ROW_ID INT , SYS_ID INT);
INSERT INTO @RowNumber(ROW_ID , SYS_ID)
SELECT
(SELECT COUNT(*) FROM @ChooseSYSID as e2 WHERE e2.SYS_ID >= e1.SYS_ID) as ROW_ID,
e1.SYS_ID
FROM @ChooseSYSID e1
/*回傳所需筆數*/
SELECT C.ROW_ID,A.SYS_ID,INFORM_ID
FROM dbo.NETBAR_HEAD A , @RowNumber C
WHERE A.SYS_ID = C.SYS_ID
AND (C.ROW_ID >= [...]
含有 ‘T-SQL’標籤(tag)的文章們
17 十一月
SQL Server 2000 RowID的作法
17 十一月
取得目前最大的IDENTITY KEY VALUE
這個回傳的值是己經加1的資料
SELECT IDENT_CURRENT(‘[SALES].[dbo].[Manual_Chapter]‘)
6 九月
TSQL: Parsing delimited string into table
From
http://rbgupta.blogspot.com/2007/10/tsql-parsing-delimited-string-into.html
ALTER FUNCTION dbo.udf_ItemParse (@Input VARCHAR(4000), @Delimeter char(1)=’|’)
RETURNS @ItemList TABLE (Item VARCHAR(200) ,RowID int)
AS
BEGIN
DECLARE @Item varchar(200)
DECLARE @StartPos int, @Length int
DECLARE @Pos int
SET @Pos = 0
WHILE LEN(@Input) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @Input)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@Input) – @StartPos – 1
IF (@StartPos > 0)
BEGIN
SET @Pos = @Pos + 1
SET @Item = [...]
6 九月
Update From Statement Example
/*資料更新回訂單主檔*/
UPDATE dbo.CN_Order_Header
SET DeliveryDate=ISNULL(hl.DeliveryDate,h.DeliveryDate),
Consignee=ISNULL(hl.Consignee,h.Consignee),
TelNum=ISNULL(hl.TelNum,h.TelNum),
DeliveryAddress=ISNULL(hl.DeliveryAddress,h.DeliveryAddress),
CheckAccount=ISNULL(hl.CheckAccount,h.CheckAccount)
FROM dbo.CN_Order_Header as h
JOIN dbo.CN_Order_Header_Log as hl
ON h.OrderID = hl.OrderID and
hl.ModifySNo = (SELECT MAX(ModifySNo)
FROM dbo.CN_Order_Header_Log
WHERE OrderID=h.OrderID)
and hl.ModifyStatus = ‘1′
and hl.OrderID=@OrderID
/*資料更新回訂單明細檔,可一次更新多筆單身*/
UPDATE dbo.CN_Order_Detail
SET ProductID=ISNULL(dl.ProductID,d.ProductID),
Unit=ISNULL(dl.Unit,d.Unit),
Qty=ISNULL(dl.Qty,d.Qty),
UnitPrice=ISNULL(dl.UnitPrice,d.UnitPrice),
Memo=ISNULL(dl.Memo,d.Memo)
FROM [...]
27 八月
Get Columns Schema In Table(SQL Server)
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
BEGIN
ALTER TABLE TEST ADD TEST_DATE DATETIME
END
26 八月
取得執行 動態SQL 的回傳值
/*版本二:已測試成功*/
IF OBJECT_ID(‘tempdb..#ERRFILE’) is not null //檢查tempdb內是否存在暫存資料表
DROP TABLE #ERRFILE //移除暫存資料表
DECLARE @CMD VARCHAR(1000)
DECLARE @EXECERROR INT
CREATE TABLE #ERRFILE(EXECERROR INT) //新增暫存資料表
SET @CMD = ‘INSERT #ERRFILE SELECT 1′ //設定動態SQL的值
EXEC (@CMD) //執行動態SQL
SET @EXECERROR = (SELECT * FROM #ERRFILE)
SELECT * FROM #ERRFILE //取得暫存資料表內的值
/*版本一:有點問題*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’#RESULT’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
CREATE TABLE #RESULT (ROW INT);
DECLARE [...]
