求存储过程

Question

我有一存储过程：
create procedure sp_liantong
@hm  varchar(20),
@id varchar(20)
as
begin
end;
 要从aaa表 (tel, hm,  id)取参数，依次插入bbb表（tel, hm,  id）
怎么 写存储过程？？

Answer 1

create   procedure   sp_liantong 
@hm     varchar(20), 
@id   varchar(20) 
as 
begin 
insert BB（tel,   hm,     id）
select tel,   hm,     id
from aaa where hm=@hm and id =@id 
end; 

Answer 2

一楼就可以
create       procedure       sp_liantong  
@hm           varchar(20),  
@id       varchar(20)  
as  
begin  
insert   BB（tel,       hm,           id）
select   tel,       hm,           id
from   aaa   where   hm=@hm   and   id   =@id  
end

Answer 3

create procedure sp_liantong
@asfzmhm  varchar(20),
@bplateid varchar(20)
as
begin
    declare @str1 varchar(1000),@str2 varchar(1000),@cnt int
    Select @str2='',@cnt=0 
    --按身份证号查询   
    select                     
        @str1=person.xm+(case person.xb when 1 then '先生,' else '女士,' end)
                    +'您的驾驶证审验日期为'+substring(rtrim(Drvinglicense.syrq),1,10)+','+'换证有效期止'+substring(rtrim(Drvinglicense.yxqz),1,10)+','
                    +'累积扣分'+rtrim(Drvinglicense.ljjf)+'分'
    from
        Drvinglicense,
        person  
    where
        Drvinglicense.sfzmhm=person.sfzmhm and Drvinglicense.sfzmhm=@asfzmhm 
    --按车牌号查询
    select
        @cnt=@cnt+1,
        @str2=@str2+','+substring(rtrim(inspedate),6,2)+'月'+substring(rtrim(inspedate),9,2)+'日在'+' '+inspeadre+' '+vehstatue
    from
        weifa
    where
        plateid=@bplateid 
    set @str1=@str1+';'+'车牌号为'+@bplateid+'的年有'+rtrim(@cnt)+'次违章'+@str2
    select @str1
end
go已经完成的存储过程
 要从aaa表   (tel,   hm,     id)取参数，依次插入bbb表（tel,   hm,     id） 
怎么   写存储过程？？ 
@asfzmhm 为hm,@bplateid为id

Answer 4

--
create  procedure  sp_liantong     
@asfzmhm     varchar(20), 
@bplateid   varchar(20)     
as     
begin     
insert into bbb(tel,hm,id)
select tel,hm,id from  aaa where hm=@asfzmhm  and id=@bplateid     
end 

Answer 5

按 你们 那 样我sp_liantong不 白做 了吗？ @str1也 没插入 bbb表 
  要从aaa表       (tel,       hm,           id)取参数，依次插入bbb表（tel,       hm,           id，@Str1）   
怎么       写存储过程？？   

Answer 6

create   procedure   sp_liantong 
@asfzmhm     varchar(20), 
@bplateid   varchar(20) 
as 
begin 
        declare   @str1   varchar(1000),@str2   varchar(1000),@cnt   int 
        Select   @str2='',@cnt=0   
        --按身份证号查询       
        select                                           
                @str1=person.xm+(case   person.xb   when   1   then   '先生,'   else   '女士,'   end) 
                                        +'您的驾驶证审验日期为'+substring(rtrim(Drvinglicense.syrq),1,10)+','+'换证有效期止'+substring(rtrim(Drvinglicense.yxqz),1,10)+',' 
                                        +'累积扣分'+rtrim(Drvinglicense.ljjf)+'分' 
        from 
                Drvinglicense, 
                person     
        where 
                Drvinglicense.sfzmhm=person.sfzmhm   and   Drvinglicense.sfzmhm=@asfzmhm   
        --按车牌号查询 
        select 
                @cnt=@cnt+1, 
                @str2=@str2+','+substring(rtrim(inspedate),6,2)+'月'+substring(rtrim(inspedate),9,2)+'日在'+'   '+inspeadre+'   '+vehstatue 
        from 
                weifa 
        where 
                plateid=@bplateid   
        set   @str1=@str1+';'+'车牌号为'+@bplateid+'的年有'+rtrim(@cnt)+'次违章'+@str2 
        select   @str1 
end 
go已经完成的存储过程 
  要从aaa表       (tel,       hm,           id)取参数，依次插入bbb表（tel,       hm,           id）   
怎么       写存储过程？？ ---
我的意思是问sp_liantong 这个存储过程跟你这aaa,bbb表有什么关系呢？

Answer 7

sp_liantong,两个输入参数从aaa(asfzmhm,bplateid)来 的 ，
aaa表(tel,asfzmhm, bplateid)取参数，依次插入bbb表（tel, asfzmhm, bplateid，msg），msg就 是sp_liantong查询 出来 的结果

Answer 8

将上面返回msg的存储过程改成一个函数：create function  wsp(@asfzmhm  varchar(20),@bplateid       varchar(20))
returns varchar(8000)   
as   
begin   
                declare @str1  varchar(1000),@str2 varchar(1000),@cnt int   
                Select  @str2='',@cnt=0       
                --按身份证号查询               
                select                                                                                       
                                @str1=person.xm+(case       person.xb       when       1       then       '先生,'       else       '女士,'       end)   
                                                                                +'您的驾驶证审验日期为'+substring(rtrim(Drvinglicense.syrq),1,10)+','+'换证有效期止'+substring(rtrim(Drvinglicense.yxqz),1,10)+','   
                                                                                +'累积扣分'+rtrim(Drvinglicense.ljjf)+'分'   
                from   
                                Drvinglicense,   
                                person           
                where   
                                Drvinglicense.sfzmhm=person.sfzmhm       and       Drvinglicense.sfzmhm=@asfzmhm       
                --按车牌号查询   
                select   
                                @cnt=@cnt+1,   
                                @str2=@str2+','+substring(rtrim(inspedate),6,2)+'月'+substring(rtrim(inspedate),9,2)+'日在'+'       '+inspeadre+'       '+vehstatue   
                from   
                                weifa   
                where   
                                plateid=@bplateid       
                set       @str1=@str1+';'+'车牌号为'+@bplateid+'的年有'+rtrim(@cnt)+'次违章'+@str2   
                return     @str1   
end   
再把aaa中所有记录插入bbb表中。调用函数得到msg
create       procedure       sp_liantong     
as   
begin
declare @tel varchar(50)
declare @asfzmhm varchar(20) 
declare @bplateid varchar(20) 
declare cur cursor for select * from aaa
open cur 
fetch next from cur into @tel,@asfzmhm,@bplateid
while(@@fetch_status=0)
begin
insert into bbb（tel,   asfzmhm,   bplateid，msg） select @tel,@asfzmhm,@bplateid,dbo.wsp(@asfzmhm,@bplateid)
fetch next from cur into @tel,@asfzmhm,@bplateid
end  
close cur
deallocate cur              
end  

Answer 9

将函数的返回参数@str1长度改一下。 刚才忘记改了
create function  wsp(@asfzmhm  varchar(20),@bplateid       varchar(20))
returns varchar(8000)   
as   
begin   
                declare @str1  varchar(8000),@str2 varchar(1000),@cnt int   
                Select  @str2='',@cnt=0       
                --按身份证号查询               
                select                                                                                       
                                @str1=person.xm+(case       person.xb       when       1       then       '先生,'       else       '女士,'       end)   
                                                                                +'您的驾驶证审验日期为'+substring(rtrim(Drvinglicense.syrq),1,10)+','+'换证有效期止'+substring(rtrim(Drvinglicense.yxqz),1,10)+','   
                                                                                +'累积扣分'+rtrim(Drvinglicense.ljjf)+'分'   
                from   
                                Drvinglicense,   
                                person           
                where   
                                Drvinglicense.sfzmhm=person.sfzmhm       and       Drvinglicense.sfzmhm=@asfzmhm       
                --按车牌号查询   
                select   
                                @cnt=@cnt+1,   
                                @str2=@str2+','+substring(rtrim(inspedate),6,2)+'月'+substring(rtrim(inspedate),9,2)+'日在'+'       '+inspeadre+'       '+vehstatue   
                from   
                                weifa   
                where   
                                plateid=@bplateid       
                set       @str1=@str1+';'+'车牌号为'+@bplateid+'的年有'+rtrim(@cnt)+'次违章'+@str2   
                return     @str1   
end