这是原来的存储过程:其中,表1里面的NO为Varchar,Pic1和Pic2这两个字段为Image字段
Create procedure Main_Copy
@new_no nvarchar(30),
@old_no nvarchar(30)asinsert into 表1 select @new_no,Pic1,Pic2, from 表1 where no=@old_no
要求:现在想在存储过程里面做四个判断假如原来的记录(where no=@old_no),Pic1和Pic2都为空的话,插入的Pic1和Pic2的值为0x0,
即插入语句为:insert into 表1 select @new_no,0x0,0x0, from 表1 where no=@old_no假如原来的记录(where no=@old_no),Pic1不为空,而Pic2为空的话,插入的Pic1的值为Pic1,Pic2的值为0x0,
即插入语句为:insert into 表1 select @new_no,Pic1,0x0, from 表1 where no=@old_no假如原来的记录(where no=@old_no),Pic1不为空,而Pic2不为空的话,插入的Pic1的值为Pic1,Pic2的值为Pic2,
即插入语句为:insert into 表1 select @new_no,Pic1,Pic2, from 表1 where no=@old_no假如原来的记录(where no=@old_no),Pic1为空,而Pic2不为空的话,插入的Pic1的值为0x0,Pic2的值为Pic2,
即插入语句为:insert into 表1 select @new_no,0x0,Pic2, from 表1 where no=@old_no请问论坛上的各位朋友,小弟的思路是这样,但具体的判断语句,存储过程的语句又该如何写呢?请各位朋友赐教,谢谢!!!
Create procedure Main_Copy
@new_no nvarchar(30),
@old_no nvarchar(30)asinsert into 表1 select @new_no,Pic1,Pic2, from 表1 where no=@old_no
要求:现在想在存储过程里面做四个判断假如原来的记录(where no=@old_no),Pic1和Pic2都为空的话,插入的Pic1和Pic2的值为0x0,
即插入语句为:insert into 表1 select @new_no,0x0,0x0, from 表1 where no=@old_no假如原来的记录(where no=@old_no),Pic1不为空,而Pic2为空的话,插入的Pic1的值为Pic1,Pic2的值为0x0,
即插入语句为:insert into 表1 select @new_no,Pic1,0x0, from 表1 where no=@old_no假如原来的记录(where no=@old_no),Pic1不为空,而Pic2不为空的话,插入的Pic1的值为Pic1,Pic2的值为Pic2,
即插入语句为:insert into 表1 select @new_no,Pic1,Pic2, from 表1 where no=@old_no假如原来的记录(where no=@old_no),Pic1为空,而Pic2不为空的话,插入的Pic1的值为0x0,Pic2的值为Pic2,
即插入语句为:insert into 表1 select @new_no,0x0,Pic2, from 表1 where no=@old_no请问论坛上的各位朋友,小弟的思路是这样,但具体的判断语句,存储过程的语句又该如何写呢?请各位朋友赐教,谢谢!!!
@new_no nvarchar(30),
@old_no nvarchar(30)asdeclare @pic1 varchar(50)
declare @pic1 varchar(50)
select @pic1=isnull(Pic1,''),@pic2=isnull(Pic2,''), from 表1 where no=@old_no
if @pic1='' and @pic2=''
begin
insert into 表1 select @new_no,'0x0','0x0', from 表1 where no=@old_no
end
if @pic1<>'' and @pic2=''
begin
insert into 表1 select @new_no,@pic1,'0x0', from 表1 where no=@old_no
end
if @pic1='' and @pic2<>''
begin
insert into 表1 select @new_no,'0x0',@pic2, from 表1 where no=@old_no
end
if @pic1<>'' and @pic2<>''
begin
insert into 表1 select @new_no,@pic1,@pic2, from 表1 where no=@old_no
end
@new_no nvarchar(30),
@old_no nvarchar(30)as
if exists (select 1 from 表1 where no=@old_no and Pic1 is null and Pic2 is null)
insert into 表1 select @new_no,0x0,0x0, from 表1 where no=@old_no
else if exists (select 1 from 表1 where no=@old_no and Pic1 is not null and Pic2 is null)
insert into 表1 select @new_no,Pic1,0x0, from 表1 where no=@old_no
else if exists (select 1 from 表1 where no=@old_no and Pic1 is not null and Pic2 is not null)
insert into 表1 select @new_no,Pic1,Pic2, from 表1 where no=@old_no
else
insert into 表1 select @new_no,0x0,Pic2, from 表1 where no=@old_no
@new_no nvarchar(30),
@old_no nvarchar(30)
as
insert into 表1 select @new_no,ISNULL(Pic1,'0x0'),ISNULL(Pic2,'0x0') from 表1 where no=@old_no
@new_no nvarchar(30),
@old_no nvarchar(30)
as
insert into 表1 select @new_no,ISNULL(Pic1,'0x0'),ISNULL(Pic2,'0x0') from 表1 where no=@old_no 这样做不是很好吗?