工作需要筆記

If 'Peter' and 'Steve' are unique in your table, this will do:

UPDATE from SELECT using SQL Server
http://stackoverflow.com/questions/2334712/update-...

SELECT m.OrderID ,left(m.productIDs,len(m.productIDs)-1) as productIDsFinal from
(SELECT OrderID,(SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details]
where OrderID = ord.OrderID
FOR XML PATH('')) as productIDs
from orders ord
GROUP BY orderid) M --這個M一定要加，不知道為啥
ORDER by M.OrderID

SELECT
ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
,*
FROM Orders

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,'

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
set @value = SUBSTRING(@valueList, @pos, @len)
--SELECT @pos, @len, @value /*this is here for debugging*/

PRINT @value
--Here is you value
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE
--DO YOUR STUFF HERE

set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END
http://techforpassion.blogspot.tw/2013/03/how-to-split-comma-separated-string-and.html

DECLARE @RowData VARCHAR(MAX);
SET @RowData = '1,2,3,;4,5,6,;7,8,9,;';

WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
SplitRows (row_data, rn) AS (
SELECT SUBSTRING(';' + @RowData + ';', n + 1,
CHARINDEX(';', ';' + @RowData + ';', n + 1) - n - 1),
n + 1 - LEN(REPLACE(LEFT(@RowData, n), ';', ''))
FROM Nums
WHERE SUBSTRING(';' + @RowData + ';', n, 1) = ';'
AND n < LEN(';' + @RowData + ';')),
SplitColumns (rn, column_data, column_idx) AS (
SELECT rn,
SUBSTRING(',' + row_data + ',', n + 1,
CHARINDEX(',', ',' + row_data + ',', n + 1) - n - 1),
n + 1 - LEN(REPLACE(LEFT(row_data, n), ',', ''))
FROM Nums
JOIN SplitRows
ON SUBSTRING(',' + row_data + ',', n, 1) = ','
AND n < LEN(',' + row_data + ',')),
Pivoted (col1, col2, col3) AS (
SELECT [1], [2], [3]
FROM SplitColumns AS S
PIVOT
(MAX(column_data) FOR column_idx IN ([1], [2], [3])) AS P)
SELECT col1, col2, col3
FROM Pivoted
WHERE col1 IS NOT NULL;

declare@id int,@stuff varchar(20)declare@tmp table(
id int notnull, stuff varchar(20)primarykey(id))insert@tmp
select id, stuff from mastertable
where condition1 > condition2

selecttop1@id=id,@stuff=stuff from@tmp

while(@@rowcount>0)beginprint@stuff
deletefrom@tmp where id=@id
selecttop1@id=id,@stuff=stuff from@tmp
end
http://stackoverflow.com/questions/18827729/tsql-perform-code-for-each-row-of-an-select

ALTER PROCEDURE [dbo].[一般搜尋EX]
@函式名稱 NVARCHAR(255) = ''
,@程式語言 NVARCHAR(255) = ''
,@起始行 INT = 0
,@結束行 INT = 0
AS
BEGIN

SET NOCOUNT ON ;

DECLARE @語法 NVARCHAR(MAX) = ''
,@語法2 NVARCHAR(MAX) = ''
,@條件 NVARCHAR(1024) = ''
,@參數 NVARCHAR(1024) = ''
,@顯示欄位 NVARCHAR(512) = ''

SET @顯示欄位 =N'SELECT *'

SET @語法 = N' FROM (SELECT ROW_NUMBER() OVER (ORDER BY 內文流水號 DESC) ROWID,X2.*,X1.目錄名稱,X3.程式語言
FROM [目錄](NOLOCK) X1
LEFT JOIN [內文](NOLOCK) X2
ON X1.目錄流水號=X2.來源目錄
LEFT JOIN [程式語言](NOLOCK) X3
ON X2.程式語言id = X3.id
WHERE (1=1){0}
) A {1}';

SET @語法2 = @語法;
SET @語法 = @顯示欄位 + @語法;

IF @函式名稱 <> '' SET @條件 += N' OR (X2.函式名稱 LIKE ''%'+@函式名稱+'%'')';
IF @程式語言 <> '' SET @條件 += N' OR (X3.程式語言 LIKE ''%'+@程式語言+'%'')';


SET @參數 = N'@函式名稱 NVARCHAR(255),@程式語言 NVARCHAR(255),@起始行 INT,@結束行 INT';


SET @語法 = REPLACE(@語法,'{0}',@條件)
SET @語法2 = REPLACE(@語法2,'{0}',@條件)

SET @語法 = REPLACE(@語法,'(1=1) OR','(1=1) AND')
SET @語法2 = REPLACE(@語法2,'(1=1) OR','(1=1) AND')


-- 分頁
IF @起始行 > @結束行
BEGIN
SET @起始行 = 0
SET @結束行 = 0
END

IF @起始行 <> 0 AND @結束行 <> 0
BEGIN
SET @語法 = REPLACE(@語法,'{1}','WHERE ROWID BETWEEN @起始行 AND @結束行')
END
ELSE
BEGIN
SET @語法 = REPLACE(@語法,'{1}','')
END

PRINT @語法
EXEC Sp_ExecuteSQL @語法,@參數
,@函式名稱 = @函式名稱
,@程式語言 = @程式語言
,@起始行 = @起始行
,@結束行 = @結束行

-- 總筆數
SET @顯示欄位 = N'SELECT ISNULL(COUNT(*),0) AS 總數量'
SET @語法2 = @顯示欄位 + @語法2
SET @語法2 = REPLACE(@語法2,'{1}','')

PRINT ''
PRINT @語法2
EXEC Sp_ExecuteSQL @語法2,@參數
,@函式名稱 = @函式名稱
,@程式語言 = @程式語言
,@起始行 = @起始行
,@結束行 = @結束行

END