SqlServer存储过程中常用函数及操作

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,
   Name
FROM Production.Product
ORDER BY ProductNumber;

• 用于update操作

UPDATE HumanResources.Employee
SET 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 AfterValue
WHERE SalariedFlag = 0

• 用户于变量赋值

    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;

• 项目实例

--根据类型，判断分配操作  构造操作日志表描述
    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

 

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.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';

• CONVERT

　　CONVERT比CAST能够转换的类型更多，更精细，经常使用是时间转换操作

　　CONVERT示例：

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

　　时间格式参考SQLServer联机文档

3、REVERSE反转函数

REVERSE函数操作表达式必须为字符类型或者可隐式转换为字符类型　　

declare @ids varchar(200)
set @ids='中国,2你2,34,56'
select @ids
Select  REVERSE(@ids)

 

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

5、其他函数
min 最小值
max 最大值
len 获得字符串长度

6、全局变量@@ROWCOUNT

　　用于获得最近一次操作受影响的行数信息。

7、exec 

• 执行拼接语句　　

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

• 调用存储过程 

　　在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

8、Try/Catch使用

• begin try开始 end try 结束try。
• begin catch 开始捕获，end catch 结束捕获。
• 常用函数：　　　

select ERROR_NUMBER() --实际错误行号
    select ERROR_SEVERITY() --错误级别
    select ERROR_STATE()  --表明错误发生的位置
    select ERROR_LINE()   --错误所在行号
    select ERROR_MESSAGE() --错误消息
    select ERROR_PROCEDURE() --导致错误的存储过程名称,嵌套时使用较方便

 　　使用try/Catch可以方便确定批处理或者存储过程中错误信息

参考资料：

　　SQLServer联机文档