0、http://www.mssqltips.com/tip.asp?tip=1294
This is VERY helpful, thanks a lot
During the Data Migration of last release, first I use “View Dependencies” , but it doesn’t cover all for omit those behind dynamic SQL.
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%'
1、
代码 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
2、declare @A as varchar(1000) SET @A = replace(( select top 10 lastname as 'data()' from t_users for xml path('')), ' ',';') print @A
但是这个replace是替换所有，这个不是我想要的，除非是ID串，名称串就做不到了...究竟要如何做MSSQL的聚合呢？
[11:29:08 AM] James: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
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 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
有一个QQ群友提供的方法，解决了空格问题！
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
3、
-- - 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
AND EXISTS (SELECT ST.element FROM udf_TxtList2Tbl(@SoftwareIds,',') ST WHERE ST.element = S.DocumentId )
4.2
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) + ' ,% '
5、
WHERE p.[Name] LIKE ISNULL('%'+@ProductName+'%', p.[Name])
小白的我觉得它很有趣 ...
6、小白的我觉得shen的这套思路很赞 ...
update T_ProductPLMs set IsPrimary = ' Y ' from ( Select ProductId from T_ProductPLMs group by ProductId having sum ( case IsPrimary when ' Y ' then 1 else 0 end ) = 0 ) a where T_ProductPLMs.PLMId = @NewPLMId and T_ProductPLMs.ProductId = a.ProductId