ASDASD
--====================== 不常用SQL語法======================--
-- 修改資料欄位型態
----------------------------------------------------------------------
ALTER TABLE ABCTABLE DROP DF__ABCTABLE__NAME__082E6AE0
ALTER TABLE ABCTABLE ALTER COLUMN NAME VARCHAR(100) NOT NULL
ALTER TABLE ABCTABLE ADD DEFAULT ('') FOR NAME
ALTER TABLE ABCTABLE DROP DF__ABCTABLE__NAME__09228F19
ALTER TABLE ABCTABLE ALTER COLUMN NAME VARCHAR(100) NOT NULL
ALTER TABLE ABCTABLE ADD DEFAULT ('') FOR PHOTO2
ALTER TABLE ABCTABLE DROP DF__ABCTABLE__NAME__0A16B352
ALTER TABLE ABCTABLE ALTER COLUMN NAME VARCHAR(100) NOT NULL
ALTER TABLE ABCTABLE ADD DEFAULT ('') FOR NAME
----------------------------------------------------------------------
-- 查詢所有sp裡有用到[關鍵]欄位
----------------------------------------------------------------------
SELECT distinct sys.sysobjects.name, sys.sysobjects.type
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%NAME%'
AND sys.sysobjects.type = 'P' --p指procedure
ORDER BY sys.sysobjects.NAME
----------------------------------------------------------------------
--查詢資料是否存在
----------------------------------------------------------------------
IF NOT EXISTS( SELECT TOP 1 * FROM AAA WITH (NOLOCK) WHERE NAME = @NAME)
BEGIN
......
END
----------------------------------------------------------------------
-- 壓縮LOG
----------------------------------------------------------------------
/*
USE [資料庫名稱]
GO
ALTER DATABASE [資料庫名稱] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(記錄檔邏輯名稱, 1)
ALTER DATABASE [資料庫名稱] SET RECOVERY FULL WITH NO_WAIT
GO
*/
USE [ABCDB]
GO
ALTER DATABASE [ABCDB] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(ABCDB_log, 1)
ALTER DATABASE [ABCDB] SET RECOVERY FULL WITH NO_WAIT
GO
----------------------------------------------------------------------
-- 查詢連線狀態
----------------------------------------------------------------------
sp_who
sp_who2
----------------------------------------------------------------------
-- 查詢連線狀態
----------------------------------------------------------------------
kill 104
----------------------------------------------------------------------
--檢查tempdb內,是否有此暫存資料表
----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#ABCTABLE') IS NOT NULL
DROP TABLE #ABCTABLE
----------------------------------------------------------------------
--交易失敗回復TRAN
----------------------------------------------------------------------
BEGIN TRAN --開始
INSERT INTO [ABCTABLE] ([AAA],[BBB],[CCC]) SELECT '1', '2'
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRAN --失敗回復
RETURN 1
END
COMMIT TRAN--成功
----------------------------------------------------------------------
--[MERGE]
----------------------------------------------------------------------
MERGE [ABCTABLE] AS A
USING (SELECT * FROM #TEMP) AS B ON A.AAA = B.AAA AND A.BBB = B.BBB
WHEN MATCHED THEN
UPDATE SET
TEL= B.TEL
, NAME= B.NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT
( AAA,BBB,TEL,NAME)
VALUES
( B.AAA, B.BBB, B.TEL, B.NAME);
--用in的方式，加快查詢速度
----------------------------------------------------------------------
SELECT TRTNID,FINCODE,MIN(FINBGDATE) AS FINBGDATE,MAX(FINENDATE) AS FINENDATE, MAX(PAYDATE) AS PAYDATE
FROM TRMTNFIN
WHERE ORGNO = @I_CHR_ORGNO
AND TRNO = @I_CHR_TRNO
AND FINCODE IN ('01','02')
AND DLTCOD = 'N'
AND FINLOCK = 'Y' --JESSIE ADD 20080214
AND TRTNID IN (SELECT DISTINCT TRTNID
FROM TRMTNFIN
WHERE ORGNO = @I_CHR_ORGNO
AND TRNO = @I_CHR_TRNO
AND FINCODE IN ('01','02')
AND DLTCOD = 'N'
AND FINLOCK = 'Y')
GROUP BY TRTNID,FINCODE
----------------------------------------------------------------------
--[CURSOR]
----------------------------------------------------------------------
DECLARE CUR_TRM CURSOR FOR
SELECT A.DCDATE,A.DCAMT,B.RATE
FROM #LEST AS A
LEFT JOIN #TRMRATE AS B ON A.DCDATE BETWEEN B.INBGDATE AND B.INENDATE
OPEN CUR_TRM
FETCH NEXT FROM CUR_TRM INTO @DIDATE, @REAMT, @RATE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DIDATE
, @RATE
, DATEDIFF(D,@FINBGDATE, @DIDATE)
, @FINBGDATE= @DIDATE
--SELECT @FINBGDATE = @DIDATE
FETCH NEXT FROM CUR_TRM INTO @DIDATE, @REAMT, @RATE
END
CLOSE CUR_TRM
DEALLOCATE CUR_TRM
----------------------------------------------------------------------
留言列表
