CREATE PROCEDURE SP_InsertNewRecord(@BCD1 Decimal,@BCD2 Decimal,@B Decimal,@C Decimal,@D Decimal) AS Insert Into Table1(A,B,C,D) Select (Case When (Select Count(*) From Table1 Where A=@BCD1)>0 Then @BCD2 Else @BCD1 End),@B,@C,@DGO
CREATE PROCEDURE usp_testqqq @aa int ,@bb int,@cc int,@dd int AS declare @count int select @count=count(*) from table where a='BCD1' if @count=0 begin insert into table select BCD1,@bb,@cc,@dd end else begin insert into table select @aa,@bb,@cc,@dd end GO
CREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS declare @count int declare @maxdd int select @maxdd=max(a) from table if @maxdd<(B*1000+C*100+D*10+X) begin insert into table select @bb*1000+@cc*100+@dd*10+X,@bb,@cc,@dd end else begin insert into table select @bb*1000+@cc*100+@dd*10+X+1,@bb,@cc,@dd end GO
CREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS declare @count int select @count=count(*) from table where a=B*1000+C*100+D*10+X if @count=0 begin insert into table select @bb*1000+@cc*100+@dd*10+X,@bb,@cc,@dd end else begin insert into table select @bb*1000+@cc*100+@dd*10+X+1,@bb,@cc,@dd end GO
/*再修改*/ CREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS declare @count int declare @maxdd int select @maxdd=max(a) from table select @count=count(*) from table where a=B*1000+C*100+D*10+X if @count=0 begin insert into table select @bb*1000+@cc*100+@dd*10+X,@bb,@cc,@dd end else begin insert into table select @maxdd+1,@bb,@cc,@dd end GO
create table tb(a int,b int,c int,d int) insert into tb select 1111,1,1,1 union all select 1231,1,2,3 union all select 1233,1,2,3 goCREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS declare @count int declare @maxdd int select @maxdd=max(a) from tb select @count=count(*) from tb where a=@bb*1000+@cc*100+@dd*10+1 select @count if @count=0 begin insert into tb select @bb*1000+@cc*100+@dd*10+1,@bb,@cc,@dd end else begin insert into tb select @maxdd+1,@bb,@cc,@dd end select * from tb GOexec usp_testqqq 2,1,1drop proc usp_testqqqdrop table tb
我会了...谢谢大家 CREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS declare @maxxx int declare @X int select @maxxx = isnull (max(a),(@bb*1000+@cc*100+@dd*10))+1 from tb where b=@bb and c=@cc and d=@dd insert into tb select @maxxx,@bb,@cc,@dd GO 我的意思是这个...
借楼上的测试数据一用;create table tb(a int,b int,c int,d int) insert into tb select 1111,1,1,1 union all select 1231,1,2,3 union all select 1232,1,2,3 goselect * from tb create procedure test (@bb int,@cc int,@dd int ) as declare @count int set @count=@bb*1000+@cc*100+@dd*10 declare @num int begin select @num=count(*) from tb where b=@bb and c=@cc and d=@dd set @num=@num+1 set @count=@bb*1000+@cc*100+@dd*10+@num insert into tb select @count,@bb,@cc,@dd end
AS
Insert Into Table1(A,B,C,D) Select (Case When (Select Count(*) From Table1 Where A=@BCD1)>0 Then @BCD2 Else @BCD1 End),@B,@C,@DGO
declare @count int
select @count=count(*) from table where a='BCD1' if @count=0
begin
insert into table
select BCD1,@bb,@cc,@dd
end
else
begin
insert into table
select @aa,@bb,@cc,@dd
end
GO
如果A=BCD2存在的话,新插入的A=BCD3以此类推
A,B,C,D都是数字,实际值A=B*1000+C*100+D*10+X
declare @count int
declare @maxdd int
select @maxdd=max(a) from table if @maxdd<(B*1000+C*100+D*10+X)
begin
insert into table
select @bb*1000+@cc*100+@dd*10+X,@bb,@cc,@dd
end
else
begin
insert into table
select @bb*1000+@cc*100+@dd*10+X+1,@bb,@cc,@dd
end
GO
插入B=1C=1D=1的时候,如果存在 A=1113则A=1114
插入B=2C=1D=1的时候,如果存在 A=2115则A=2116
插入的A是在给出的BCD的值之后判断的
declare @count int
select @count=count(*) from table where a=B*1000+C*100+D*10+X if @count=0
begin
insert into table
select @bb*1000+@cc*100+@dd*10+X,@bb,@cc,@dd
end
else
begin
insert into table
select @bb*1000+@cc*100+@dd*10+X+1,@bb,@cc,@dd
end
GO
CREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS
declare @count int
declare @maxdd int
select @maxdd=max(a) from table
select @count=count(*) from table where a=B*1000+C*100+D*10+X if @count=0
begin
insert into table
select @bb*1000+@cc*100+@dd*10+X,@bb,@cc,@dd
end
else
begin
insert into table
select @maxdd+1,@bb,@cc,@dd
end
GO
insert into tb
select 1111,1,1,1 union all
select 1231,1,2,3 union all
select 1233,1,2,3
goCREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS
declare @count int
declare @maxdd int
select @maxdd=max(a) from tb
select @count=count(*) from tb where a=@bb*1000+@cc*100+@dd*10+1
select @count
if @count=0
begin
insert into tb
select @bb*1000+@cc*100+@dd*10+1,@bb,@cc,@dd
end
else
begin
insert into tb
select @maxdd+1,@bb,@cc,@dd
end
select * from tb
GOexec usp_testqqq 2,1,1drop proc usp_testqqqdrop table tb
CREATE PROCEDURE usp_testqqq @bb int,@cc int,@dd int AS
declare @maxxx int
declare @X int
select @maxxx = isnull (max(a),(@bb*1000+@cc*100+@dd*10))+1 from tb where b=@bb and c=@cc and d=@dd
insert into tb
select @maxxx,@bb,@cc,@dd
GO
我的意思是这个...
借楼上的测试数据一用;create table tb(a int,b int,c int,d int)
insert into tb
select 1111,1,1,1 union all
select 1231,1,2,3 union all
select 1232,1,2,3
goselect * from tb
create procedure test (@bb int,@cc int,@dd int )
as
declare @count int
set @count=@bb*1000+@cc*100+@dd*10
declare @num int
begin
select @num=count(*) from tb where b=@bb and c=@cc and d=@dd
set @num=@num+1
set @count=@bb*1000+@cc*100+@dd*10+@num
insert into tb select @count,@bb,@cc,@dd
end