SqlServer預存程序中常用函數及操作

標籤：before   convert   字串   popd   tor   logs   mount   schema   resource   

1、case語句

• 用於選擇語句　　　　

SELECT   ProductNumber, Category =      CASE ProductLine         WHEN ‘R‘ THEN ‘Road‘         WHEN ‘M‘ THEN ‘Mountain‘         WHEN ‘T‘ THEN ‘Touring‘         WHEN ‘S‘ THEN ‘Other sale items‘         ELSE ‘Not for sale‘      END,   NameFROM Production.ProductORDER BY ProductNumber;

View Code

• 用於update操作

UPDATE HumanResources.EmployeeSET VacationHours =     ( CASE         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40         ELSE (VacationHours + 20.00)       END    )OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,        Inserted.VacationHours AS AfterValueWHERE SalariedFlag = 0

View Code

• 使用者於變數賦值

    SET @ContactType =         CASE             -- Check for employee            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e                 WHERE e.BusinessEntityID = @BusinessEntityID)                 THEN ‘Employee‘            -- Check for vendor            WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec                WHERE bec.BusinessEntityID = @BusinessEntityID)                 THEN ‘Vendor‘            -- Check for store            WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v                          WHERE v.BusinessEntityID = @BusinessEntityID)                 THEN ‘Store Contact‘            -- Check for individual consumer            WHEN EXISTS(SELECT * FROM Sales.Customer AS c                 WHERE c.PersonID = @BusinessEntityID)                 THEN ‘Consumer‘        END;

View Code

• 項目執行個體

--根據類型，判斷分配操作  構造動作記錄表描述    set @tempopdes=case @servertype when ‘0‘ then ‘分配客戶‘                                    when ‘1‘ then ‘分配帳號‘                                    when ‘2‘ then ‘分配帳號‘                                    when ‘4‘ then ‘分配客戶‘                                    when ‘5‘ then ‘分配客戶‘                                    when ‘3‘ then ‘分配客戶‘                                    when ‘7‘ then ‘分配客戶‘                                    else ‘‘                                end

View Code

 

2、cast和convert函數

CAST ( expression AS data_type [ ( length ) ] )CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

• CAST

　　主要在字串拼接時轉換運算式類型(expression)類型，常用把int或其他數實值型別轉換為varchar類型。

　   CAST樣本：　　

SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPriceFROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductIDWHERE Name LIKE ‘Long-Sleeve Logo Jersey, M‘;

View Code

• CONVERT

　　CONVERT比CAST能夠轉換的類型更多，更精細，經常使用是時間轉換操作

　　CONVERT樣本：

 CONVERT(nvarchar(30), GETDATE(), 126)

View Code

　　時間格式參考SQLServer聯機文檔

3、REVERSE反轉函數

REVERSE函數動作表達式必須為字元類型或者可隱式轉換為字元類型　　

declare @ids varchar(200)set @ids=‘中國,2你2,34,56‘select @idsSelect  REVERSE(@ids)

View Code

 

4、CHARINDEX函數

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

在expression2中從[start_location]（未設定或者為負數時從0開始）開始尋找expression1的位置資訊，找到後立刻返回位置的值。開始位置計數為1

DECLARE @document varchar(64)SELECT @document = ‘bicycle Reflectors are vital safety‘ +                   ‘ bicycle components of your bicycle.‘SELECT CHARINDEX(‘bicycle‘, @document)GO

View Code

5、其他函數
min 最小值
max 最大值
len 獲得字串長度

6、全域變數@@ROWCOUNT

　　用於獲得最近一次操作受影響的行數資訊。

7、exec 

• 執行拼接語句　　

 EXECUTE (‘ALTER INDEX ALL ON ‘ + @schemaname + ‘.‘ + @tablename + ‘ REBUILD;‘);

View Code

• 調用預存程序 

　　在exec sp_executesql @sql中變數 @sql必須聲名為nvarchar類型

set @sql=‘select @a=SUM(mi_receivermoney) from t_money_receive where  mr_id in (‘+@mr_ids+‘)‘        exec sp_executesql @sql,N‘@a decimal(18,2) output‘,@receivetotalmoney output

View Code

　　　

參考資料：

　　SQLServer聯機文檔

SqlServer預存程序中常用函數及操作