sqlserver函数与过程（二）

过程

• SQLserver 过程是具有特定功能，可多次对数据表操作的独立模块。
• 返回值通常用return 返回整数 0，1…。(可选）
• 也可通过output 参数或select 语句返回结果集。

1.过程的定义

本过程定义了一个过程，输入一个动态SQL语句，将结果行集组成一个SQL命令串，返回结果。该过程使用Function 是不能完成的。

CREATE PROCEDURE [dbo].[getCmd]
	@CursorStr nvarchar(max),
	@Str nvarchar(max)='' out
as
begin
	set nocount on;
	declare @s nvarchar(max)='';
	set @CursorStr=' declare Cur cursor for '+@CursorStr;
	exec sp_executeSql @CursorStr;
	
	open Cur;
	FETCH NEXT FROM Cur INTO @s;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Str=@Str+@s;
		FETCH NEXT FROM  Cur INTO @s; 
	end
	close Cur;--关闭标量库
	deallocate Cur;--释放光标空间  
	 
	return 0;
end

2.过程的调用

DECLARE @IndexSQL NVARCHAR(MAX);
declare @ic nvarchar(max);
set @ic=@oldDb+'.sys.index_columns';
--declare @c nvarchar(max);
set @c=@oldDb+'.sys.columns';
declare @i nvarchar(max);
set @i=@oldDb+'.sys.indexes';
--declare @t nvarchar(max);
set @t=@oldDb+'.sys.tables';
SET @IndexSQL = '';
set @cmd='
SELECT  ''CREATE '' + 
                 CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + 
                 i.type_desc + '' INDEX '' + QUOTENAME(i.name) + 
                 '' ON '+@newDb+'.dbo.'' + QUOTENAME(t.name) + '' ('' + 
                 STUFF((
                     SELECT '', '' + QUOTENAME(c.name)
                     FROM '+@ic+' ic
                     JOIN '+@c+' c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
                     WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
                     ORDER BY ic.key_ordinal
                     FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') + 
                 '')'' + 
                 CASE WHEN EXISTS (
                     SELECT *
                     FROM '+@ic+' ic
                     JOIN zwdb.sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
                     WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
                 ) THEN 
                     '' INCLUDE ('' + 
                     STUFF((
                         SELECT '', '' + QUOTENAME(c.name)
                         FROM '+@ic+' ic
                         JOIN '+@c+' c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
                         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
                         ORDER BY ic.index_column_id
                         FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') + 
                     '')'' 
                 ELSE '''' END + '';'' + CHAR(13) + CHAR(10) COLLATE Chinese_PRC_CI_AS AS combined_column 
FROM '+@i+' i
JOIN '+@t+' t ON i.object_id = t.object_id
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND t.is_ms_shipped = 0 AND i.index_id > 0;';
exec zwdb.dbo.getCmd @CursorStr=@cmd,@Str=@IndexSQL output ;
IF @IndexSQL <> ''
    EXEC sp_executesql @IndexSQL;

总结

1. 数据修改能力

(1)标量函数：

• 不允许修改数据（如 INSERT、UPDATE、DELETE）。
• 只能读取数据，保持函数的确定性（相同输入始终返回相同输出）。

(2)存储过程：

• 允许修改数据，支持事务处理（如 BEGIN TRANSACTION）。
• 可执行任何 T-SQL 语句，包括动态 SQL。

2. 性能与优化

(1)标量函数：

• 性能较低，尤其在 WHERE 子句中频繁调用时，可能导致全表扫描。
• 适合简单计算，避免复杂逻辑。

(2)存储过程：

• 性能较高，执行计划可缓存，减少编译开销。
• 适合复杂业务逻辑（如批量数据处理）。

3. 应用场景

(1)标量函数：

• 数据计算（如格式化日期、字符串处理）。
• 在查询中作为表达式使用（如 SELECT、JOIN 条件）。

(2)存储过程：

• 业务逻辑封装（如用户认证、订单处理）。
• 数据修改操作（如批量插入、事务处理）。
• 跨数据库操作或调用外部资源（如调用 API）。

4. 其他差异

总结

建议：

优先使用存储过程处理业务逻辑，使用标量函数处理简单计算，避免在大型数据集上频繁调用函数。