SQL Server存储过程
定义:
提前存好的程序
常用系统的存储过程:
创建存储过程:
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS [ begin ] T-SQL 语句 [ end ]
无参存储:
if exists (select * from sysobjects where name = 'pp_qsl') drop procedure pp_qsl /*判断是否存在该存储过程,存在则删除 */ go /*防止出现批处理错误*/ create procedure pp_qsl as select * from ceshi.dbo.[360_qsl] /*执行程序*/ go
带参存储,无参数输出:
create procedure [dbo].[pp_sql] (@StartDate nvarchar(50),@EndDate nvarchar(50), @default nvarchar(50) = '默认值') /*@StartDate: 初始日期 @EndDate: 结束日期 @default : 默认值。如果输入参数,则以输入参数为准,未输入参数,则为默认值。必须放到最后 */ AS DECLARE @LMStartDate nvarchar(50) /*定义变量*/ select @LMStartDate = dateadd(MONTH ,-1,@StartDate) /*变量赋值*/ select * from [360_Mobile_Search] /*执行语句*/
带参存储,输出参数:
if exists (select * from sysobjects where name = 'pp_qsl') drop procedure pp_qsl /*判断是否存在该存储过程,存在则删除 */ go create proc pp_qsl @num int output /*创建输出变量,必须带output */ as declare @number int select @number = count(*) from ceshi.dbo.[58TongCheng] /*变量赋值 */ set @num = @number /*选择变量,可以说输出 */ select @num select * from ceshi.dbo.[58TongCheng] /*结果集,可以输出 */ exec pp_qsl @num = 0 /*执行存储过程,若未给变量赋值,则取默认值*/
调用参数:
EXEC procedure_name 参数
案例:1-100 相加值:
CREATE procedure dbo.sum_add (@start_number int, -- 初始值 @end_number int, -- 结束值 @sum_number int output -- 结果初始值 ) AS BEGIN while(@start_number<= @end_number) begin set @sum_number=@sum_number+@start_number set @start_number = @start_number+1 end SELECT @sum_number as 和 END