本文记录sqlserver 创建存储过程及基本语法的讲解，附上作者实现功能的实例

背景：作者的task是需要两个数据库之间进行数据迁移，本可以用java程序来实现，后面觉得可以学习下存储过程，故采用这种方式

实例：上代码

if(exists(select * from sysobjects where name='sp_data_move' ))
    drop proc sp_data_move
go
--声明存储过程
create procedure sp_data_move
            @staffChannelId varchar(36), 
            @agencyChannelId varchar(36), 
            @medicalChanneId varchar(36)
as
--声明变量
declare @lms_id varchar(50), @lms_realname varchar(50), @lms_phone varchar(50), @lms_email varchar(50), @lms_work_no varchar(50),
@lms_open_id varchar(50), @lms_province varchar(50), @lms_city varchar(50), @lms_company varchar(50), @lms_job_title varchar(50), 
@lms_department varchar(50), @lms_user_type int, @lms_dist_name varchar(50), @lms_create_time date, @lms_update_time date,
@lms_privacy_flag int, @lms_privacy_time date, @lms_promotion_flag int, @lms_promotion_time date

declare @app_id varchar(50), @form_id varchar(50), @form_name varchar(50), @form_title varchar(50), @social_campaign_id varchar(50),
@channel_id varchar(50), @form_url varchar(50), @privacy_flag varchar(50), @opt_in_type varchar(50), @is_send_sfdc int
--定义游标
declare lms_data_cursor cursor for (select su.id, su.realname, su.phone, su.email, su.work_no, lu.open_id, lu.province, lu.city, lu.company, lu.job_title, 
              lu.department, lu.user_type, lu.dist_name, lu.create_time, lu.update_time, luc.privacy_flag, luc.privacy_time, luc.promotion_flag, 
              luc.promotion_time from lms_sys_user su, lms_user lu, lms_user_consent luc where su.id = lu.sys_user_id and su.id = luc.sys_user_id)
--打开游标
open lms_data_cursor
--开始循环游标
fetch next from lms_data_cursor into @lms_id, @lms_realname, @lms_phone, @lms_email, @lms_work_no, @lms_open_id, @lms_province, @lms_city, @lms_company,
@lms_job_title, @lms_department, @lms_user_type, @lms_dist_name, @lms_create_time, @lms_update_time, @lms_privacy_flag, @lms_privacy_time,
@lms_promotion_flag, @lms_promotion_time
while @@fetch_status = 0
begin
--编写逻辑语句
--判断此用户存不存在wep中wc_weixin_user,如不在则添加openid
declare @num int, @weixinsql nvarchar(max)
set @weixinsql = 'select @a=count(*) from wc_weixin_user where open_id =@b' 
exec sp_executesql @weixinsql, N'@b varchar(50),@a int output',@b=@lms_open_id, @a=@num output
if @num = 0 begin
insert into wc_weixin_user(open_id, app_id, subscribe) values(@lms_open_id, '', 1)
end
--根据usertype不同身份对应不同表单下的渠道id
declare @channel_value varchar(50)
select @channel_value = 
case 
    when @lms_user_type = 0 then @staffChannelId
    when @lms_user_type = 1 then @agencyChannelId
    when @lms_user_type = 2 then @medicalChanneId
    else ''
end
--根据渠道id查询表单相关信息
declare @queryformsql nvarchar(max)
set @queryformsql = 'select @d=sf.app_id, @e=sf.form_id, @f=sf.form_name, @g=sf.form_title, @h=sf.social_campaign_id, @i=sc.channel_id, @j=sc.form_url
                            from smf_channel sc, smf_form sf where sc.form_id = sf.form_id and sc.channel_id = @k'
exec sp_executesql @queryformsql, N'@k varchar(50), @d varchar(50) output, @e varchar(50) output, @f varchar(50) output, @g varchar(50) output, 
@h varchar(50) output, @i varchar(50) output, @j varchar(50) output', @k=@channel_value, @d=@app_id output, @e=@form_id output, @f=@form_name output,
@g=@form_title output,@h=@social_campaign_id output, @i=@channel_id output, @j=@form_url output
--数据做转换
if @lms_privacy_flag = 1 begin
set @privacy_flag = '是'
end
else begin
set @privacy_flag = '否'
end

if @lms_promotion_flag = 1 begin
set @opt_in_type = '是'
end
else begin
set @opt_in_type = '否'
end

if @social_campaign_id is null begin
set @is_send_sfdc = 0
end
else begin
set @is_send_sfdc = 1
end
--插入smf_form_data表数据
insert into smf_form_data(id, form_id, form_name, form_url, form_title, social_campaign_id, channel_id, open_id, submit_time, update_time
, is_send_sfdc, is_sync_leads, name, phone, email, province, city, company_name, job_title, depart, employee_number, agency_name, privacy_flag
, privacy_time, opt_in_type, opt_in_time) values (@lms_id, @form_id, @form_name, @form_url, @form_title, @social_campaign_id, @channel_id, @lms_open_id
, @lms_create_time, @lms_update_time, @is_send_sfdc, 0, @lms_realname, @lms_phone, @lms_email, @lms_province, @lms_city, @lms_company, @lms_job_title
, @lms_department, @lms_work_no, @lms_dist_name, @privacy_flag, @lms_privacy_time, @opt_in_type, @lms_promotion_time)
--判断此用户在smf_user_profile表中是否有基本信息，没有添加，有的话修改
declare @count int, @profilesql nvarchar(max)
set @profilesql = 'select @x=count(*) from smf_user_profile where open_id = @y;'
exec sp_executesql @profilesql, N'@y varchar(50),@x int output', @y=@lms_open_id,@x=@count output
if @count = 0 begin
insert into smf_user_profile(open_id, app_id, name, phone, email, province, city, company_name, job_title, depart, employee_number, agency_name) 
values(@lms_open_id, @app_id, @lms_realname, @lms_phone, @lms_email, @lms_province, @lms_city, @lms_company, @lms_job_title, @lms_department, 
@lms_work_no, @lms_dist_name)
end
fetch next from lms_data_cursor into @lms_id, @lms_realname, @lms_phone, @lms_email, @lms_work_no, @lms_open_id, @lms_province, @lms_city, @lms_company,
@lms_job_title, @lms_department, @lms_user_type, @lms_dist_name, @lms_create_time, @lms_update_time, @lms_privacy_flag, @lms_privacy_time,
@lms_promotion_flag, @lms_promotion_time
end
close lms_data_cursor
deallocate lms_data_cursor

下面作者会根据代码实现来讲解一下使用到的基本语法，可能不全，只是用到多少讲多少

1.if(exists(select * from sysobjects where name='sp_data_move' ))
    drop proc sp_data_move
go

和建表语句一样，先检查后创建

2.create procedure sp_data_move
            @staffChannelId varchar(36), 
            @agencyChannelId varchar(36), 
            @medicalChanneId varchar(36)
as

创建存储过程语法，注意：参数必须使用@开头，后面跟上类型和长度

3.declare @lms_id varchar(50), @lms_realname varchar(50), @lms_phone varchar(50), @lms_email varchar(50), @lms_work_no varchar(50),
@lms_open_id varchar(50), @lms_province varchar(50), @lms_city varchar(50), @lms_company varchar(50), @lms_job_title varchar(50), 
@lms_department varchar(50), @lms_user_type int, @lms_dist_name varchar(50), @lms_create_time date, @lms_update_time date,
@lms_privacy_flag int, @lms_privacy_time date, @lms_promotion_flag int, @lms_promotion_time date

变量的声明，格式和参数一致，可用declare 声明多个参数，逗号隔开，也可一个一个声明

如：

declare @lms_id varchar(50)

declare @lms_realname varchar(50)

声明变量也可在后续用到时再声明 如：

declare @count int, @profilesql nvarchar(max)
set @profilesql = 'select @x=count(*) from smf_user_profile where open_id = @y;'

此处需注意，声明sql时类型必须使用nvarchar类型

4.使用游标循环，由于代码比较繁琐，字段较多，下面改动简单例子进行展示

--定义游标
declare lms_data_cursor cursor for (select a,b,c from tableA)
--打开游标
open lms_data_cursor
--开始循环游标
fetch next from lms_data_cursor into @a, @b, @c
while @@fetch_status = 0
begin
--编写逻辑语句
fetch next from lms_data_cursor into @a, @b, @c
end
close lms_data_cursor
deallocate lms_data_cursor

5.执行动态sql,传参和反参

declare @num int, @weixinsql nvarchar(max)
set @weixinsql = 'select @a=count(*) from wc_weixin_user where open_id =@b' 
exec sp_executesql @weixinsql, N'@b varchar(50),@a int output',@b=@lms_open_id, @a=@num output

首先声明出参和入参的变量名，然后动态sql中替换,使用临时变量

使用exec sp_executesql @weixinsql执行sql

N'@b varchar(50),@a int output'定义临时变量为入参还是出餐，默认为入参故@b不需指定

@b=@lms_open_id, @a=@num output将临时变量赋值声明的变量，后续逻辑即可使用

6.流程控制语法

本文中使用了if语句流程，很简单，看例子即可，中间也可以加入 else if 条件 begin

if @lms_promotion_flag = 1 begin
set @opt_in_type = '是'
end
else begin
set @opt_in_type = '否'
end

还使用了case when语句流程，实例简单易懂

declare @channel_value varchar(50)
select @channel_value = 
case 
    when @lms_user_type = 0 then @staffChannelId
    when @lms_user_type = 1 then @agencyChannelId
    when @lms_user_type = 2 then @medicalChanneId
    else ''
end

7.静态sql可以直接执行，SQL中拼接参数即可执行

由于作者开发过程中任务需求需要用到存储过程，故查阅资料学习语法，完成功能开发，也是第一次开发sqlserver存储过程，有些复杂的场景逻辑中可能没涉及到，暂时没深入研究，但是本文介绍的应对简单的存储过程开发足够了，希望对各位有帮助！