摘要:test
'--------------------------------------------------------------------------------------------------------------------------------------------------------------'
--example of create table
--如果table 已存在 就drop了它
if exists (select * from dbo.sysobjects
where id = object_id(N'Lab_Request_Cancel_Reason')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Lab_Request_Cancel_Reason
GO
create table Lab_Request_Cancel_Reason
(
Seq_No SeqNo not null,
Cancel_Reason Desc120 not null,
LastUpdate datetime not null
)
grant all privileges on Lab_Request_Cancel_Reason to HISWG
ALTER TABLE Lab_Request_Cancel_Reason add constraint PK_Lab_Request_Cancel_Reason PRIMARY KEY NonClustered(Seq_No)
ALTER TABLE Lab_Request_Cancel_Reason add constraint FK_Lab_Request_Cancel_Reason foreign key(Cancel_Reason) references Lab_Specimen(Cancel_Reason)
--DROP CONSTRAINT PK_Lab_Request_Cancel_Reason
-- Disable the constraint.
ALTER TABLE Lab_Request NOCHECK CONSTRAINT PK_Lab_Request ;
-- Reenable the constraint.
ALTER TABLE Lab_Request WITH CHECK CHECK CONSTRAINT PK_Lab_Request ;
ALTER TABLE Lab_Request add Status1 tinyint null
ALTER TABLE Lab_Request alter column Status1 tinyint not null
ALTER TABLE Lab_Request drop COLUMN Status1
INSERT INTO HISFUNCTION VALUES('TCRPT001', 'Print Sick Cert', getdate(), 1, null)
Insert into Workgroup_Function_Privilege (Workgroup_Code, Func_Code, LastUpdate)
values ('ACDWG', 'ACRPT124', '17-06-2008 11:03:41')
UPDATE HIS_USER SET DEPT_CODE = 'EEC'
WHERE Create_DT between '07-04-2009 00:00:00' And '07-04-2009 23:59:59'
Delete from Workgroup_Function_Privilege
where Workgroup_Code = 'ACDWG'
and Func_Code = 'ACRPT106'
'--------------------------------------------------------------------------------------------------------------------------------------------------------------'
--example of sp_executesql
CREATE PROCEDURE usp_OPRPT033Sub
@Tablename sysname,
@Patient_No SysNo
AS
--
--DECLARE @Tablename AS sysname
--DECLARE @Patient_No AS SysNo
--
--set @Tablename ='patient_log'
--set @Patient_No ='PN20060600645'
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = 'Select Patient_No,Patient_Surname,Patient_Given_Name,Patient_IDNo,Patient_Sex,Patient_DOB,User_Code,WorkStation_Name,Record_Type,LastUpdate,ChiName,Ref_Patient_No,Created_By,Created_DT,Lock_WS_Name,Lock_User_Code' +
' from ' + QUOTENAME(@Tablename) +
' Where Patient_No = ' + QUOTENAME(@Patient_No,'''')
EXEC sp_executesql @cmd;
go
GRANT EXECUTE ON dbo.usp_OPRPT033Sub TO HISWG
GO
--exec usp_OPRPT033Sub 'patient_log','PN20030100020'
'--------------------------------------------------------------------------------------------------------------------------------------------------------------'
--Transaction
set implicit_transactions on
go
Update Lab_Request Set Status = 8 Where Request_No = 'LR20090100004'
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
set implicit_transactions off
go
------------------------------
-- 3步曲
begin tran
rollback tran
commit tran
------------------------------
--Index
create index indVisit_No on Lab_Request(Visit_No)
drop index indRequest_DT on Lab_Request
ALTER INDEX ind_Request_DT ON Request_No DISABLE;
------------------------------
--sp_rename
EXEC sp_rename N'Lab_Request.PKRequest_No', N'PK_New_Request_No', N'INDEX';
EXEC sp_rename 'Lab_Request.Is_Blood_Bank','Is_Blood_Transfusion','COLUMN'
EXEC sp_rename 'OLDNAME','NEWNAME','OBJECT'
EXEC sp_rename 'Xray_Region_Default_Item','Xray_Region_Projection','OBJECT'
------------------------------
--sysobjects
select * from sysobjects where name like '%injection%'
------------------------------
--[習題]拆解資料表的某一個欄位，分成兩欄
--EG1
SELECT * FROM T1
INNER JOIN T2 ON SUBSTRING(T1.AA,1,5) = T2.AA
--EG2
SELECT * FROM a
,(
SELECT substring( b.id, 1, length( b.id ) -2 ) AS b1
, substring( b.id , -2 ) AS b2
, name AS b3
FROM b
) AS bb
WHERE a.id = bb.b1
--如果ID編號長度不一，只能依靠％符號來拆字串了。
--EG3
SELECT * FROM a
, ( SELECT substring( b.id, 1, locate( '%', b.id ) -1 ) AS b1
, substring( b.id, locate( '%', b.id ) +1 ) AS b2
, name AS b3 FROM b
) AS bb
WHERE a.id = bb.b1
------------------------------
--DATEDIFF
--EG1
update xray_report_header set xray_report_header.age = DATEDIFF(yy,PV.PV_DOB,xray_report_header.Report_DT)
--EG2
DATEDIFF(day, b.LastUpdate, '12-11-2008')
------------------------------
--Select Case
--EG1
Select Username = User_Full_Name
,Status1 = case status2 when 0 then 'aaa'
when 1 then 'bbb'
else 'ccc' end
,Endo_Right_Desc = case Endoscopy_rights1 when 1 then 'OGD, '
else '' end
+ case Endoscopy_rights2 when 1 then 'Colonoscopy, '
else '' end
From HIS_user
--EG2
CASE 函數：
SELECT ProductID, Name,
CASE Class
WHEN 'H' THEN ROUND( (ListPrice * .6), 2)
WHEN 'L' THEN ROUND( (ListPrice * .7), 2)
WHEN 'M' THEN ROUND( (ListPrice * .8), 2)
ELSE ROUND( (ListPrice * .9), 2)
END AS DiscountPrice
FROM Production.Product
------------------------------
--Store procedure
if exists (select * from sysobjects where id = object_id('dbo.usp_MRRPT035') and sysstat & 0xf = 4)
drop procedure dbo.usp_MRRPT035
GO
CREATE PROCEDURE usp_MRRPT035
@StartDate datetime,
@EndDate datetime,
@Validation bit
AS
Declare @spv_PV_Type_IP as integer
Declare @spv_PV_Status_Cancelled as integer
Set @spv_PV_Type_IP = 1
Set @spv_PV_Status_Cancelled = 8
Select
PV.patient_No,
PV.visit_No,
PV.PV_Visit_DT,
PV.PV_Discharge_DT,
PV.Discharge_Reason_Code,
Diag_Code = PVD.Diag_Code,
PV.PV_Sex,
PV.PV_DOB
INTO #tempPatientVisit
From Patient_Visit PV (index = indPV_Discharge_DT),
PV_diagnosis PVD (index = PK_PV_diagnosis)
Where PV.Visit_No = PVD.Visit_No And
PV.PV_Discharge_DT between @StartDate and @EndDate and
PV.PV_Type = @spv_PV_Type_IP and
PV.PV_Status <> @spv_PV_Status_Cancelled and
PV.Visit_No like 'Z%'
INSERT INTO #tempPatientVisit
Select PV.patient_No,
PV.visit_No,
PV.PV_Visit_DT,
PV.PV_Discharge_DT,
PV.Discharge_Reason_Code,
Diag_Code = PVD.Diag_Code,
PV.PV_Sex,
PV.PV_DOB
From Patient_Visit PV (index = indPV_Discharge_DT),
PV_diagnosis PVD (index = PK_PV_diagnosis)
Where PV.Visit_No = PVD.Visit_No And
PV.PV_Discharge_DT between @StartDate and @EndDate and
PV.PV_Type = @spv_PV_Type_IP and
PV.PV_Status <> @spv_PV_Status_Cancelled and
PV.Visit_No like 'BB%' and
PV.patient_No not in (select patient_No from #tempPatientVisit)
--select * from #tempPatientVisit where #tempPatientVisit.visit_No not in (Select PV.Visit_No,Inst_Code = 11,PV.PV_Visit_DT,PV.PV_Discharge_DT,PV.Discharge_Reason_Code,PVD.Diag_Code,PV.PV_Sex,PV.PV_DOB From Patient_Visit PV (index = indPV_Discharge_DT) , PV_Diagnosis PVD (index = PK_PV_Diagnosis) Where PV.Visit_No = PVD.Visit_No And PV.PV_Status <> 8 And PV.PV_Type = 1 And PV.PV_Discharge_DT between '01-01-2008 00:00:00' And '01-02-2008 23:59:59' And (PV.Visit_No like 'HN%' or PV.Visit_No like 'Z%'))
Select PV.Visit_No,Inst_Code = 11,PV.PV_Visit_DT,PV.PV_Discharge_DT,PV.Discharge_Reason_Code,PVD.Diag_Code,PV.PV_Sex,PV.PV_DOB
INTO #tableA
From Patient_Visit PV (index = indPV_Discharge_DT) , PV_Diagnosis PVD (index = PK_PV_Diagnosis)
Where PV.Visit_No = PVD.Visit_No And PV.PV_Status <> 8 And PV.PV_Type = 1 And PV.PV_Discharge_DT between '01-01-2008 00:00:00' And '01-02-2008 23:59:59' And (PV.Visit_No like 'HN%' or PV.Visit_No like 'Z%')
select * from #tempPatientVisit
select * from #tableA where #tableA.PV_Discharge_DT not in (select PV_Discharge_DT from #tempPatientVisit)
select * from #tableA where #tableA.PV_Visit_DT not in (select PV_Visit_DT from #tempPatientVisit)
GO
GRANT EXECUTE ON dbo.usp_MRRPT035 TO HISWG
GO
------------------------------
--TOP
select top (10) * from doctor
where doctor_surname like 'm%'
------------------------------
--convert
convert(varchar(12),expiryDate,106) as expiryDate
convert(nvarchar, a.SDITM)
------------------------------
--View
USE Northwind
GO
CREATE VIEW EmployeeName AS
SELECT EmployeeID, LastName, FirstName
FROM Northwind.dbo.Employees
GO
/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName,OrderID, OrderDate
FROM Northwind.dbo.Orders AS Ord
JOIN Northwind.dbo.EmployeeName as EmpN
ON (Ord.EmployeeID = EmpN.EmployeeID)
WHERE OrderDate > '31 May, 1996'
/* SELECT referencing the Employees table directly. */
SELECT LastName AS EmployeeLastName,OrderID, OrderDate
FROM Northwind.dbo.Orders AS Ord
JOIN Northwind.dbo.Employees as Emp
ON (Ord.EmployeeID = Emp.EmployeeID)
WHERE OrderDate > '31 May, 1996'
------------------------------
-- Example PROCEDURE
USE Northwind
GO
DROP PROCEDURE OrderSummary
GO
CREATE PROCEDURE OrderSummary
@MaxQuantity INT OUTPUT AS
-- SELECT to return a result set summarizing
-- employee sales.
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID
-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]
-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO
-- Test the stored procedure.
-- DECLARE variables to hold the return code
-- and output parameter.
DECLARE @OrderSum INT
DECLARE @LargestOrder INT
-- Execute the procedure, which returns
-- the result set from the first SELECT.
EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT
-- Use the return code and output parameter.
PRINT 'The size of the largest single order was: ' +
CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
CONVERT(CHAR(6), @OrderSum)
GO
------------------------------
-- Example FUNCTION
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)
------------------------------
--在選取清單中指定資料行時也可以指定別名 (例如，proj_sales AS "Projected Sales") 或其他的運
算式，例如 (price = price * 1.15，或是 SUM(SalesAmount))。
在數值資料行或常數上使用算術運算子或函數的計算或運算：
SELECT ROUND( (ListPrice * .9), 2) AS DiscountPrice
FROM Production.Product
WHERE ProductID = 58
資料類型轉換：
SELECT ( CAST(ProductID AS VARCHAR(10)) + ': '
+ Name ) AS ProductIDName
FROM Product
子查詢：
SELECT Prd.ProductID, Prd.Name,
( SELECT SUM(OD.UnitPrice * OD.OrderQty)
FROM AdventureWorks.Sales.SalesOrderDetail AS OD
WHERE OD.ProductID = Prd.ProductID
) AS SumOfSales
FROM AdventureWorks.Production.Product AS Prd
ORDER BY Prd.ProductID
------------------------------
--
--FOR SQL Server
SELECT ID, AMT,
CONVERT(VARCHAR(3), AMT * 100 / SUM(AMT) OVER (PARTITION BY NULL) ) + '%
'AS PERC
FROM JEFF
** CTE範例 **
--如果CTE的WITH不在第一列, 前方要加上;;
WITH DIYParts_BOM(PartName, Parent, Level, SortCol)
AS(
--Recursive CTE分為兩個部分, 第一部分為Anchor Member
--指不會被遞迴呼叫到的部分
SELECT PartName, Parent, 0, CONVERT(nvarchar(128),PartNo)
FROM DIYParts
WHERE Parent=N'ROOT'
UNION ALL
--UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
--直到無任何查詢結果為止
SELECT P.PartName, P.Parent, B.Level+1, CONVERT(nvarchar(128), B.SortCol+'-'+CONVERT(nvarchar(128),P.PartNo))
FROM DIYParts P, DIYParts_BOM B
WHERE P.Parent=B.PartName
)
SELECT REPLICATE(' ',Level) + PartName, Level, SortCol
FROM DIYParts_BOM
ORDER BY SortCol
------------------------------
--嚴肅的結論是: 在正式環境中，不要隨便褻玩這種超大型的資料庫。但一個好玩的議題是，資料存在SQL Server中，SQL豈有不知Table資料筆數的道理，應該有其他更有效率的方法查資料筆數吧? 憶起當年追隨SQL大師Ryan時，他曾露了一手由PK筆數查總筆數的方法，查詢語法如下:
SELECT
dbo.sysobjects.name as 'Object Name',
dbo.sysindexes.rowcnt as 'Row Count',
dbo.sysindexes.name as 'Index Name'
FROM dbo.sysobjects INNER JOIN dbo.sysindexes
ON dbo.sysobjects.id = dbo.sysindexes.id
and dbo.sysobjects.name='financial_values' --Change the Table name here
and dbo.sysindexes.indid=1 --(1:for clustered index, 0:for non-clustered index)
------------------------------
--row_number
select a.UserName,a.UserID from (select row_number() over (order by UserID) as UID,UserName,UserID from UserDataInfo where bmi>20) as a where a.UID between @SP and @EP
------------------------------
--密碼
select hashbytes('MD5','12345') as MD5,hashbytes('SHA1','12345') as SHA1;
CREATE TABLE [dbo].[HashTest](
[INDEX_NO] [int] IDENTITY(1,1) NOT NULL,
[PassWD] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[HashedPW] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
CONSTRAINT [PK_HashTest] PRIMARY KEY CLUSTERED
(
[INDEX_NO] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
update hashtest set hashedPW = HashBytes('SHA1', PassWD)
update hashtest set hashedPW = sys.fn_VarBinToHexStr(HashBytes('SHA1', PassWD));
------------------------------
--IDENTITY_INSERT ??
SET IDENTITY_INSERT XRay_Region_Testing ON -- 這個 a 是 TableName
Insert into XRay_Region_Testing (Region_No,Region_Desc) Values(1,'Testing3')
SET IDENTITY_INSERT XRay_Region_Testing OFF
------------------------------
--charindex
--但是,除了用like外,還有一個更方便且快速的語法,
--因為客戶只需要輸入甚麼就是甚麼.所以他不會需要像是輸入"中_航空"就找出有"中國航空"與"中華航空"的資料,
--所以直接使用下面的語法就可以解決這問題,而且搜尋的速度也更加快速了
--charindex會傳回字串在資料欄位中的位置,所以當欄位內容有資料時,傳回的資料就會大於0,
--用此來取得搜尋的資料會比用like來的快速.而且不會有特殊字元的問題(有的話應該也只有單引號...).
select * from TableName where charindex(SeachKey,fieldName) > 0
select * from XRay_Region_Testing where charindex('test',Region_Desc) > 0
--找字串入面的特殊字元
select * from tableName where charindex('%',fieldName) > 0
select * from tableName where columnName like '%\%%' escape '\'
或寫成
select * from tableName where columnName like '%\%%' {escape '\'}
select * from tableName where columnName like '%[%]%'
------------------------------
--下面是MSSQL,隨機撈資料的方法
select top 10 * from table_name order by newid()
------------------------------
--While
declare @No int
declare @NoOfRequestNo int
declare @lsRequestNo char(13)
declare @NoOfSameRequestNo int
declare @Counter int
set @No=1
Declare @tempRequestNo table(
Request_No SysNo
)
insert into @tempRequestNo
select distinct Request_No from XRay_Request_Detail
select @NoOfRequestNo = count(*)from @tempRequestNo
while @No<=@NoOfRequestNo
begin
set @counter = 1
while @counter<=@NoOfSameRequestNo
begin
set @counter=@counter+1
end
set @No=@No+1
end
------------------------------
--若要檢視資料表的資料行名稱，您可以使用 sp_help 或是下列其中一個查詢：
SELECT name
FROM sys.columns
WHERE OBJECT_ID IN
(SELECT OBJECT_ID ('table_name'))
或 SELECT TOP 0 * FROM table_name
------------------------------
--OTHER
select * from Service_Type_Item a, Stock b
where a.stock_code = b.stock_code
and b.stock_type_code in ('PAI','PDI','PIJ','PVC')
sp_helptext usp_ROERPT001
sp_spaceused Dr_General_Letter
sp_password 'currentPSWD', 'newPSWD' ,'loginName'
sp_who3
sp_helpdb
sp_help 'DataBaseName'
exec sp_spaceused 'dbo.TbVote'
select * from sys.objects where name like '%queue%'
and b.WKDATE > dateadd(month,-3,getdate())
select scope_identity() --??
SET ROWCOUNT 10 --??
--???
--USE hisdev
--GO
--EXEC sp_updatestats
--
--USE hisdev
--DBCC UPDATEUSAGE (0);
--GO
--
--
--USE master
--GO
--EXEC sp_MSforeachdb @command1="print '?' EXEC [?].dbo.sp_updatestats"
------------------
熱愛生命 喜愛新奇 有趣的事物
過去 是無法改變
將來 卻能夠創造
希望使大家生活更便利
世界更美好
a guy who loves IT and life