MSSQL开发点滴(5/2010)
2010-05-26 by 嘉瑜, Category: 码农日记, Tags: SQL, 开发点滴 No Comments 13次浏览
行列转换（CharIndex、Pivot，for XML）
Outer Apply，
如何查询MS SQL中的依赖关系？
一段Try-Catch / Transaction / Cusor的Store Procedure
1. 行列转换, 有两种方法，
a) 使用charIndex和分隔符的trick
SELECT * FROM [TBL] WHERE CHARINDEX(',' + convert(varchar(max),D.DocumentId )+ ',', ',' + @DocumentIDs + ',') > 0
SELECT * FROM tbname WHERE CHARINDEX(','+RTRIM(fdname)+',',','+@idlist+',')>0
SELECT * FROM tbname WHERE PATINDEX('%,'+RTRIM(fdname)+',%',','+@idlist+',')>0
SELECT * FROM tbname WHERE ','+@idlist+',' LIKE '%,'+RTRIM(fdname)+',%'
b) 使用user-defined function将comma-separated string转换为表
--- converts ntext list to individual item in a table
ALTER FUNCTION [dbo].[udf_TxtList2Tbl] (@list NTEXT,@separator CHAR(1))
RETURNS @tbl TABLE (element NVARCHAR(300) NOT NULL) AS
BEGIN
DECLARE @pos INT,
@textpos INT,
@nextpos INT,
@str NVARCHAR(4000),
@tmpstr NVARCHAR(4000),
@leftover NVARCHAR(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= DATALENGTH(@list) / 2
BEGIN
SET @nextpos = @textpos + 4000 - DATALENGTH(@leftover) / 2
SET @tmpstr = LTRIM(@leftover + SUBSTRING(@list, @textpos, @nextpos - 1))
SET @textpos = @nextpos
SET @pos = CHARINDEX(@separator, @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (element) VALUES(RTRIM(LTRIM(@str)))
SET @tmpstr = LTRIM(SUBSTRING(@tmpstr, @pos + 1, LEN(@tmpstr)))
SET @pos = CHARINDEX(@separator, @tmpstr)
END
SET @leftover = @tmpstr
END
SET @leftover = LTRIM(RTRIM(@leftover))
IF @leftover <> ''
INSERT @tbl (element) VALUES(@leftover)
RETURN
END
使用方法：EXISTS (SELECT ST.element FROM udf_TxtList2Tbl(@SoftwareIds,’,') ST WHERE ST.element = S.DocumentId )
3）PIVOT http://www.cnblogs.com/geovindu/archive/2010/04/16/1713366.html
2. 很详尽的图文文档，如何查询SQL对象的依赖关系，以及如何找到SQL对象是否包含某段文字
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '% in (@%)%'
AND ROUTINE_TYPE='PROCEDURE'
SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE charindex('t_swrequests', text) > 0
-------
select distinct specific_name from information_schema.routines
where lower(routine_definition) like '%t_swrequests%'
3. 列值合并，可以查询“邹建”以获取更多经典SQL，这个会经常在项目中使用到：
首先准备好测试数据库
CREATE TABLE [dbo].[Table1](
[id] [int] NULL,
[name] [varchar](50) NULL,
[school] [varchar](50) NULL,
[year] [int] NULL,
[score] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO table1 (id,name,school,year,score) VALUES (1,'Jerra','sc1', 2002, 100)
INSERT INTO table1 (id,name,school,year,score) VALUES (2,'Jerra','sc2', 2004, 60)
INSERT INTO table1 (id,name,school,year,score) VALUES (3,'Tom','sc3', 2002, 40)
INSERT INTO table1 (id,name,school,year,score) VALUES (4,'Jerry','sc4', 2004, 90)
INSERT INTO table1 (id,name,school,year,score) VALUES (5,'Tom','sc5', 2005, 80)
GO
a) 使用for XML
select name,
replace(( select school as 'data()'
from table1
for
xml path('')
), ' ', '/') as school,
replace(( select year as 'data()'
from table1
for
xml path('')
), ' ', '/') as year,
sum(score) as score
from table1
group by name
这是一段更复杂一点的的替换，使用了outer apply
UPDATE table1 SET school=REPLACE(school,'sc','sc ')
GO
SELECT *
FROM
(
SELECT name,SUM(score) AS score FROM table1 GROUP BY name
) AS A
OUTER APPLY
(
SELECT school = STUFF(REPLACE((REPLACE((SELECT school FROM dbo.table1 N WHERE NAME = A.name FOR XML AUTO),'<N school="','/')),'"/>',''),1,1,'')
,[year] = STUFF(REPLACE((REPLACE((SELECT [year] FROM dbo.table1 N WHERE NAME = A.name FOR XML AUTO),'<N year="','/')),'"/>',''),1,1,'')
) N
4. 动态SQL的LIKE参数
动态SQL因为有SQL注入的不安全性,故除非必要,尽量须避免,LIKE参数,可直接传入’%text%’ 即可
5. 一段cursor/transaction/exception的SQL存储过程
ALTER PROCEDURE [dbo].[Usp_processswgrouprequest] @GrpSWRequestId INT
AS
DECLARE @UpdateTime DATETIME
DECLARE @Count INT
DECLARE @CompanyId INT
DECLARE @SWRequestStatusId INT
DECLARE @SWApprovalStatusPending INT;
DECLARE @SWRequestId INT
DECLARE @Err INT
SELECT @UpdateTime = Getdate()
SET @Err = 0
SET @SWRequestStatusId = 1 -- RequestStatusPendingApproval
SET @SWApprovalStatusPending = 2
SET @Count=0
DECLARE curSWRequest CURSOR FOR
SELECT DISTINCT V_Users.CompanyId
FROM T_SWRequestUsers
INNER JOIN V_Users
ON T_SWRequestUsers.UserId = V_Users.UserId
WHERE ( T_SWRequestUsers.SWRequestId = @GrpSWRequestId )
BEGIN TRANSACTION
OPEN curSWRequest
FETCH NEXT FROM curSWRequest INTO @CompanyId
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- insert the SWRequest for each company
-- PRINT @CompanyId
SET @UpdateTime = Dateadd(ms, @Count, Getdate())
INSERT INTO T_SWRequests
(RequesterId,
RequestDate,
SWRequestStatusId,
Routed,
CompanyId,
GroupId,
ContactFirstName,
ContactLastName,
ContactEmail,
CCEmails,
OtherInformation,
UpdateBy,
UpdateTime,
ParentSWRequestId,
NotifyUsers)
SELECT RequesterId,
@UpdateTime,
@SWRequestStatusId,
'Y',
@CompanyId,
NULL,
ContactFirstName,
ContactLastName,
ContactEmail,
CCEmails,
OtherInformation,
UpdateBy,
@UpdateTime,
@GrpSWRequestId,
NotifyUsers
FROM T_SWRequests
WHERE SWRequestId = @GrpSWRequestId
IF @@ERROR != 0
BEGIN
ROLLBACK WORK
RAISERROR('usp_ProcessGroupRequest:Error In Inserting record to T_SWRequest. Transaction is aborted.',
16,
1)
GOTO end_program
END
SET @SWRequestId = @@IDENTITY
-- PRINT @SWRequestId
-- Insert Users
INSERT INTO T_SWRequestUsers
(SWRequestId,
UserId,
UpdateBy,
UpdateTime)
SELECT @SWRequestId,
T_SWRequestUsers.UserId,
T_SWRequestUsers.UpdateBy,
@UpdateTime
FROM T_SWRequestUsers
INNER JOIN V_Users
ON T_SWRequestUsers.UserId = V_Users.UserId
WHERE ( T_SWRequestUsers.SWRequestId = @GrpSWRequestId )
AND V_Users.CompanyId = @CompanyId
IF @@ERROR != 0
BEGIN
ROLLBACK WORK
RAISERROR('usp_ProcessGroupRequest:Error In Inserting record to T_SWRequestUsers. Transaction is aborted.',
16,
1)
GOTO end_program
END
-- Insert SLAs
INSERT INTO T_SWRequestSLAs
(SWRequestId,
SLAId,
UpdateBy,
UpdateTime)
SELECT DISTINCT @SWRequestId,
T_SWRequestSLAs.SLAId,
T_SWRequestSLAs.UpdateBy,
@UpdateTime
FROM T_SWRequestSLAs
INNER JOIN T_GroupSLA
ON T_SWRequestSLAs.SLAId = T_GroupSLA.SLAId
WHERE ( T_SWRequestSLAs.SWRequestId = @GrpSWRequestId )
AND T_GroupSLA.CompanyId = @CompanyId
IF @@ERROR != 0
BEGIN
ROLLBACK WORK
RAISERROR('usp_ProcessGroupRequest:Error In Inserting record to T_SWRequestSLA. Transaction is aborted.',
16,
1)
GOTO end_program
END
-- Insert Softwares
INSERT INTO T_RequestSWs
(SWRequestId,
DocumentId,
SWApprovalStatusId,
RequestedBy,
UpdateBy,
UpdateTime)
SELECT @SWRequestId,
DocumentId,
@SWApprovalStatusPending,
RequestedBy,
UpdateBy,
@UpdateTime
FROM T_RequestSWs
WHERE SWRequestId = @GrpSWRequestId
IF @@ERROR != 0
BEGIN
ROLLBACK WORK
RAISERROR('usp_ProcessGroupRequest:Error In Inserting record to T_RequestSWs. Transaction is aborted.',
16,
1)
GOTO end_program
END
SET @Count = @Count + 1
FETCH NEXT FROM curSWRequest INTO @CompanyId
END
UPDATE T_SWRequests
SET Routed = 'Y',
SWRequestStatusId = 2
WHERE SWRequestId = @GrpSWRequestId
COMMIT WORK
END_PROGRAM:
CLOSE curSWRequest
DEALLOCATE curSWRequest
RETURN
6. 可以利用MSSQL2005的Try-Catch，自定义错误信息，并在C#代码中捕捉到
