首页 > 代码库 > SqlServer存储过程中常用函数及操作
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, NameFROM Production.ProductORDER BY ProductNumber;
- 用于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
- 用户于变量赋值
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.UnitPriceFROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductIDWHERE 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 @idsSelect 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
参考资料：
SQLServer联机文档
SqlServer存储过程中常用函数及操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。