求之不得，辗转反侧，特来求一复杂的存储过程（插入数据时有返回），有请各位高手。

Question

sql05中表tb有列（如下所示）：
id(int主键、自增)、xh(char,12)、 kh(char,12)、  lb(char,1)、xxdm(char,3)、sfzh(char,18)
1                  010444000001  010444000001   3           444      123456789012345678
2                  910658000329  910658000329   2           444      123756789012345678
3                  010444000002  010444000002   3           444      123456789012345     
4                  018444444345  018444444345   1           359      126456789012345678
5                  555555550321  010460000001   3           460      153456789012345678
6                  660029000888  010359000001   3           359      123456789012345678     
7                  010444000003  010444000003   4           444      123456889012345678
8                  010444000004  010444000004   3           444      123456789012345678
9                  010444000005  010444000005   3           444      123456789012345     
10                 577777770345  577777770345   2           359      123456289012345678
11                 999999999999  999999999999   2           460      123456789012345678
12                 010359000002  010359000002   3           359      123456789012345678     
其中xh、lb、xxdm、sfzh为(在点击input.aspx按钮提交数据时)插入到表中的，kh是自动生成的，现求一生成kh并有返回值的存储过程，要求：当lb为'1'或'2'时kh=xh的值，当lb为'3'或'4'时，kh值为'010'+xxdm+从1开始的8位递增序号，并且在插入数据时如果xh重复拒绝插入并给予警告提示，sfzh重复时给出警告但允许插入，同时把生成的kh返回给页面的标签Label的text。（带c#调用最好）谢谢。

Answer 1

create  table tb(id int identity(1,1),xh varchar(12),kh varchar(12),lb varchar(1),xxdm varchar(3),sfzh varchar(18))
insert into tb(xh,kh,lb,xxdm,sfzh)
select '010444000001','010444000001','3','444','123456789012345678' union all
select '910658000329','910658000329','2','444','123756789012345678' union all
select '010444000002','010444000002','3','444','123456789012345' union all
select '018444444345','018444444345','1','359','126456789012345678' union all
select '555555550321','010460000001','3','460','153456789012345678' union all
select '660029000888','010359000001','3','359','123456789012345678' union all
select '010444000003','010444000003','4','444','123456889012345678' union all
select '010444000004','010444000004','3','444','123456789012345678' union all
select '010444000005','010444000005','3','444','123456789012345' union all
select '577777770345','577777770345','2','359','123456289012345678' union all
select '999999999999','999999999999','2','460','123456789012345678' union all
select '010359000002','010359000002','3','359','123456789012345678'
给出测试数据。

Answer 2

-- 苦力活，呵呵， Money.....银子......

Answer 3

create proc getkh
(
@xh varchar(12),
@kh varchar(12),
@lb varchar(1),
@xxdm varchar(3),
@sfzh varchar(18)
)
as
begin
if(@lb=1 or @lb=2)
select @xh 'kh'
if(@lb=3 or @lb=4)
begin
        declare @max08 int
        select @max08=max(xxdm) from tb where xxdm like '010'+@xxdm+'%'
select cast(@max08 as bigint)+1 'kh'
end
if((select count(1) from tb where xh=@xh)>0)
select 'xh重复'
if((select count(1) from tb where sfzh=@sfzh)>0)
begin
select 'sfzh重复'
insert into tb(xh,kh,lb,xxdm,sfzh)
select @xh,@kh,@lb,@xxdm,@sfzh
end
end

Answer 4

谢谢各位：不好意思，没有弄个这个，在网上看了些资料，还是弄不出来，
谢谢maco_wang，我忘了说了：当lb为'1'或'2'时kh=xh的值必须填写插入，当lb为'3'或'4'时，kh值不允许插入，实际插入tb中的数据是
create  table tb(id int identity(1,1),xh varchar(12),kh varchar(12),lb varchar(1),xxdm varchar(3),sfzh varchar(18))
insert into tb(xh,kh,lb,xxdm,sfzh)
select '010444000001','','3','444','123456789012345678' union all
select '910658000329','910658000329','2','444','123756789012345678' union all
select '010444000002','','3','444','123456789012345' union all
select '018444444345','018444444345','1','359','126456789012345678' union all
select '555555550321','','3','460','153456789012345678' union all
select '660029000888','','3','359','123456789012345678' union all
select '010444000003','','4','444','123456889012345678' union all
select '010444000004','','3','444','123456789012345678' union all
select '010444000005','','3','444','123456789012345' union all
select '577777770345','577777770345','2','359','123456289012345678' union all
select '999999999999','999999999999','2','460','123456789012345678' union all
select '010359000002','','3','359','123456789012345678'

Answer 5

create proc p_test @xh char(12),@lb char(1),@xxdm char(3),@sfzh char(18),@rt int output
as
begin
declare @kh char (12)
set @rt=1
if exists(select 1 from tb where xh=@xh)
begin
set @rt=-1--  -1 表示xh重复拒绝插入并给予警告提示，一般用c#调用根据这个值进行提示
return
end
if exists(select 1 from tb where sfzh=@sfzh)
begin
set @rt=0--  0 sfzh重复时给出警告但允许插入，一般用c#调用根据这个值进行提示
end
if @lb='1' or @lb='2'
begin
set @kh=@xh
end
if @lb='3' or @lb='4'
begin
select @kh=max(kh) from tb where lb in ('3','4') and xxdm=@xxdm
if @kh is null
begin
set @kh='010'+@xxdm+'000001'
end
else
begin
set @kh=left(@kh,6)+right('0000000'+ltrim((right(@kh,8)+1)),8)
end
end
insert tb (xh,kh,lb,xxdm,sfzh) select @xh,@kh,@lb,@xxdm,@sfzh
if @@error<>0 
set @rt=-2 --存储过程执行失败，此时程序会捕获到sqlserver的异常
end

Answer 6

你把@lb='3' or @lb='4'
那段去掉就行了

Answer 7

谢谢你：xys_777我测试提示：操作失败！ 
麻烦你看看调用代码哪里错误？

Answer 8

麻烦你看看调用代码哪里错误？ 
 protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection conn = DB.createConnection();
        SqlCommand mycom = new SqlCommand("p_test", conn);
        if (mycom.Connection.State == ConnectionState.Closed)
            mycom.Connection.Open();
        mycom.CommandType = CommandType.StoredProcedure;
        mycom.Parameters.Add(new SqlParameter("@xh", SqlDbType.Char, 12));
        mycom.Parameters.Add(new SqlParameter("@kh", SqlDbType.Char, 12));
        mycom.Parameters.Add(new SqlParameter("@lb", SqlDbType.Char, 1));
        mycom.Parameters.Add(new SqlParameter("@xxdm", SqlDbType.Char, 3));
        mycom.Parameters.Add(new SqlParameter("@sfzh", SqlDbType.Char, 18));        mycom.Parameters.Add(new SqlParameter("@rt", SqlDbType.Int));
        mycom.Parameters["@rt"].Direction = ParameterDirection.ReturnValue;
        mycom.Parameters["@xh"].Value = xh.Text;
        mycom.Parameters["@kh"].Value = kh.Text;
        mycom.Parameters["@lb"].Value = lb.Text;
        mycom.Parameters["@xxdm"].Value = xxdm.Text;
        mycom.Parameters["@sfzh"].Value = sfzh.Text;        try
        {
            mycom.ExecuteScalar();
        }
        catch (SqlException ee)
        {
            Response.Write("操作失败！");
            return;
        }
        finally
        {
            mycom.Connection.Close();
        }
        string temp = mycom.Parameters["@rt"].Value.ToString();        switch (temp)
        {                case "1":
                Response.Write("添加成功！");
                break;
            case "0":
                Response.Write("添加成功，但身份证号重复！");
                break;
           case "-1":
               Response.Write("序号重复！");
                break;
            case "-2":
                Response.Write("数据操作错误！"); 
                break;
        }
  
        conn.Close();
    }

Answer 9

提示：
操作失败！过程或函数 'p_test' 需要参数 '@rt'，但未提供该参数。
如果把ReturnValue换成Output则提示：
操作失败！为过程或函数 p_test 指定了过多的参数。
以下是调用代码，请指教，问题在哪里？protected void Button1_Click(object sender, EventArgs e)
  {
  SqlConnection conn = DB.createConnection();
  SqlCommand mycom = new SqlCommand("p_test", conn);
  if (mycom.Connection.State == ConnectionState.Closed)
  mycom.Connection.Open();
  mycom.CommandType = CommandType.StoredProcedure;
  mycom.Parameters.Add(new SqlParameter("@xh", SqlDbType.Char, 12));
  mycom.Parameters.Add(new SqlParameter("@kh", SqlDbType.Char, 12));
  mycom.Parameters.Add(new SqlParameter("@lb", SqlDbType.Char, 1));
  mycom.Parameters.Add(new SqlParameter("@xxdm", SqlDbType.Char, 3));
  mycom.Parameters.Add(new SqlParameter("@sfzh", SqlDbType.Char, 18));  mycom.Parameters.Add(new SqlParameter("@rt", SqlDbType.Int));
  mycom.Parameters["@rt"].Direction = ParameterDirection.ReturnValue;//这里请注意
  mycom.Parameters["@xh"].Value = xh.Text;
  mycom.Parameters["@kh"].Value = kh.Text;
  mycom.Parameters["@lb"].Value = lb.Text;
  mycom.Parameters["@xxdm"].Value = xxdm.Text;
  mycom.Parameters["@sfzh"].Value = sfzh.Text;  try
  {
  mycom.ExecuteScalar();
  }
  catch (SqlException ee)
  {
  Response.Write("操作失败！");
  return;
  }
  finally
  {
  mycom.Connection.Close();
  }
  string temp = mycom.Parameters["@rt"].Value.ToString();  switch (temp)
  {     case "1":
  Response.Write("添加成功！");
  break;
  case "0":
  Response.Write("添加成功，但身份证号重复！");
  break;
  case "-1":
  Response.Write("序号重复！");
  break;
  case "-2":
  Response.Write("数据操作错误！");  
  break;
  }
   
  conn.Close();
  }

Answer 10

上面的问题解决了，还剩一个问题没有解决：怎样把生成的kh返回给页面的标签Label的text，？

Answer 11

create proc p_test @xh char(12),@lb char(1),@xxdm char(3),@sfzh char(18),@rt int OUTPUT,
@kh char (12) OUT
as
begin
set @rt=1
if exists(select 1 from tb where xh=@xh)
begin
    set @rt=-1--  -1 表示xh重复拒绝插入并给予警告提示，一般用c#调用根据这个值进行提示
    return
end
if exists(select 1 from tb where sfzh=@sfzh)
begin
    set @rt=0--  0 sfzh重复时给出警告但允许插入，一般用c#调用根据这个值进行提示
end
if @lb='1' or @lb='2'
begin
    set @kh=@xh
end
if @lb='3' or @lb='4'
begin
    select @kh=max(kh) from tb where lb in ('3','4') and xxdm=@xxdm
    if @kh is null
    begin
        set @kh='010'+@xxdm+'000001'
    end
    else
    begin
        set @kh=left(@kh,6)+right('0000000'+ltrim((right(@kh,8)+1)),8)
    end
end
insert tb (xh,kh,lb,xxdm,sfzh) select @xh,@kh,@lb,@xxdm,@sfzh
if @@error<>0 
set @rt=-2 --存储过程执行失败，此时程序会捕获到sqlserver的异常
end

Answer 12

那你在存储过程里多加一个output参数好了，或者最后用 select @kh然后得到存储过程的结果集

Answer 13

是不是只能fill到一个datatable里，我看我们的c#程序是这样写得

Answer 14

多谢，辛苦了！
提示：
操作失败！从数据类型 char 转换为 int 时出错。是不是这里有问题？
      SqlCommand mycom = new SqlCommand("p_test", conn);
        mycom.Connection.Open();
        mycom.CommandType = CommandType.StoredProcedure;
        mycom.Parameters.Add(new SqlParameter("@xh", SqlDbType.Char, 12));
        mycom.Parameters.Add(new SqlParameter("@zkzh", SqlDbType.Char, 12));
        mycom.Parameters.Add(new SqlParameter("@lb", SqlDbType.Char, 1));
        mycom.Parameters.Add(new SqlParameter("@xxdm", SqlDbType.Char, 3));
        mycom.Parameters.Add(new SqlParameter("@sfzh", SqlDbType.Char, 18));
        mycom.Parameters.Add(new SqlParameter("@rt", SqlDbType.Int));
        mycom.Parameters["@rt"].Direction = ParameterDirection.Output;
        mycom.Parameters.Add(new SqlParameter("@kh", SqlDbType.Int));
        mycom.Parameters["@kh"].Direction = ParameterDirection.Output;
        mycom.Parameters["@xh"].Value = xh.Text;
        mycom.Parameters["@zkzh"].Value = zkzh.Text;
        mycom.Parameters["@lb"].Value = lb.Text;
        mycom.Parameters["@xxdm"].Value = xxdm.Text;
        mycom.Parameters["@sfzh"].Value = sfzh.Text;        try
        {
            mycom.ExecuteScalar();
        }
        catch (SqlException ee)
        {
            Response.Write("操作失败！" + ee.Message.ToString());
            return;
        }
        finally
......    Label.Text = mycom.Parameters["@kh"].Value.ToString().Trim(); 

Answer 15

mycom.Parameters.Add(new SqlParameter("@kh", SqlDbType.Int));
这个参数应该是char