SQL Server 2000 RowID的作法

利用這個作法的話，最近一筆會出現在最上面，順序會反過來
/*取得使用者的查詢結果*/
DECLARE @ChooseSYSID TABLE (SYS_ID INT);
INSERT INTO @ChooseSYSID(SYS_ID)
SELECT SYS_ID FROM dbo.NETBAR
WHERE (@INFORM_ID = ‘ ‘       OR INFORM_ID LIKE @INFORM_ID)
ORDER BY SYS_ID DESC
/*產生ROWID*/
DECLARE @RowNumber TABLE(ROW_ID INT , SYS_ID INT);
INSERT INTO @RowNumber(ROW_ID , SYS_ID)
SELECT
(SELECT COUNT(*) FROM @ChooseSYSID as e2 WHERE e2.SYS_ID >= e1.SYS_ID) as ROW_ID,
e1.SYS_ID
FROM @ChooseSYSID e1
/*回傳所需筆數*/
SELECT C.ROW_ID,A.SYS_ID,INFORM_ID
FROM dbo.NETBAR_HEAD A , @RowNumber C
WHERE A.SYS_ID = C.SYS_ID
AND (C.ROW_ID >= [...]

Continue reading »

取得目前最大的IDENTITY KEY VALUE

這個回傳的值是己經加1的資料
SELECT IDENT_CURRENT(‘[SALES].[dbo].[Manual_Chapter]‘)

Continue reading »

取得預存程序回傳的值

private int ExecuteStoredProcedure(string storedProcedureName)
{
SqlConnection connection = new SqlConnection(connectionString);
// Command – specify as StoredProcedure
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
// Return value as parameter
SqlParameter returnValue = new SqlParameter(“returnVal”, SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
[...]

Continue reading »

查詢資料表欄位資訊

查詢資料表欄位資訊…
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘資料表名稱’

Continue reading »

ASP Connection to Sql Server Example

‘Create DB Connection
set Conn = Server.CreateObject(“ADODB.Connection”)
Conn.Open  “Provider=SQLOLEDB;Data Source=GBUTEST-DB;Initial Catalog=Northwind;User Id=sa;Password=XXXXX;”
‘Create ADO SQL RecordSet
set rs = Server.CreateObject(“ADODB.recordset”)
rs.Open “SELECT [ShipperID],[CompanyName],[Phone] FROM [Northwind].[dbo].[Shippers]“, Conn
response.write(“<TABLE BORDER=1> <TR BGCOLOR=lightyellow>”)
‘Print Table Header
For each h in rs.fields
response.write “<TD>” & h.Name & “</TD>”
next
response.write(“</TR>”)
‘Extract Data From RecordSet
rs.MoveFirst
While Not rs.EOF
response.write(“<TR>”)
for each x in rs.fields
‘response.write(x.name)
‘response.write(” = “)
response.write “<td>” & x.value & “</td>”
next
rs.MoveNext
response.write(“</TR>”)
Wend
response.write(“</TR></TABLE>”)
Conn.Close
Set Conn=Nothing

Continue reading »

TSQL: Parsing delimited string into table

From
http://rbgupta.blogspot.com/2007/10/tsql-parsing-delimited-string-into.html
ALTER FUNCTION dbo.udf_ItemParse (@Input VARCHAR(4000), @Delimeter char(1)=’|’)
RETURNS @ItemList TABLE (Item VARCHAR(200) ,RowID int)
AS
BEGIN
DECLARE @Item varchar(200)
DECLARE @StartPos int, @Length int
DECLARE @Pos int
SET @Pos = 0
WHILE LEN(@Input) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @Input)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@Input) – @StartPos – 1
IF (@StartPos > 0)
BEGIN
SET @Pos = @Pos + 1
SET @Item = [...]

Continue reading »

Update From Statement Example

/*資料更新回訂單主檔*/
UPDATE dbo.CN_Order_Header
SET DeliveryDate=ISNULL(hl.DeliveryDate,h.DeliveryDate),
Consignee=ISNULL(hl.Consignee,h.Consignee),
TelNum=ISNULL(hl.TelNum,h.TelNum),
DeliveryAddress=ISNULL(hl.DeliveryAddress,h.DeliveryAddress),
CheckAccount=ISNULL(hl.CheckAccount,h.CheckAccount)
FROM dbo.CN_Order_Header as h
JOIN dbo.CN_Order_Header_Log as hl
ON h.OrderID = hl.OrderID and
hl.ModifySNo = (SELECT MAX(ModifySNo)
FROM dbo.CN_Order_Header_Log
WHERE OrderID=h.OrderID)
and hl.ModifyStatus = ‘1′
and hl.OrderID=@OrderID
/*資料更新回訂單明細檔,可一次更新多筆單身*/
UPDATE dbo.CN_Order_Detail
SET ProductID=ISNULL(dl.ProductID,d.ProductID),
Unit=ISNULL(dl.Unit,d.Unit),
Qty=ISNULL(dl.Qty,d.Qty),
UnitPrice=ISNULL(dl.UnitPrice,d.UnitPrice),
Memo=ISNULL(dl.Memo,d.Memo)
FROM [...]

Continue reading »

Get Columns Schema In Table(SQL Server)

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
BEGIN
ALTER TABLE TEST ADD TEST_DATE DATETIME
END

Continue reading »

取得執行 動態SQL 的回傳值

/*版本二:已測試成功*/
IF OBJECT_ID(‘tempdb..#ERRFILE’) is not null  //檢查tempdb內是否存在暫存資料表
DROP TABLE #ERRFILE                                                //移除暫存資料表
DECLARE @CMD VARCHAR(1000)
DECLARE @EXECERROR INT
CREATE TABLE #ERRFILE(EXECERROR INT)     //新增暫存資料表
SET @CMD = ‘INSERT #ERRFILE SELECT 1′         //設定動態SQL的值
EXEC (@CMD)                                                                    //執行動態SQL
SET @EXECERROR = (SELECT * FROM #ERRFILE)
SELECT * FROM #ERRFILE                                         //取得暫存資料表內的值
/*版本一:有點問題*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’#RESULT’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
CREATE TABLE #RESULT (ROW INT);
DECLARE [...]

Continue reading »