解析存储过程,高手帮忙!!
CREATE   proc   P_AddSNO
@pc   varchar(50),
@pm   varchar(200)= '% ',
@ps   varchar(50)= '% ',
@SNO   varchar(50),
@pid   varchar(50)= ' '
as
if   Exists(select   *   from   ProductBaseInfo   where   pc=@pc   and   pm   like   @pm   and   ps   like   @ps)
begin
    if   Exists(select   *   from   Storage   where   pclass=@pc   and   StorageNo=@SNO   and   pmodel   like   @pm   and   pstyle   like   @ps   and   productID=@pid)
    begin
        select   Result= 'Storage   Postion   be   exist '
        return
    end
    else
    begin
        if   @pid <> ' '
        begin
            Insert   into   Storage(pclass,pmodel,pstyle,StorageNo,productID)
            values(@pc,@pm,@ps,@sno,@pid)
        end    
        else
        begin
            Insert   into   Storage(pclass,pmodel,pstyle,StorageNo)
            select   distinct   pc,pm,ps,StorageNo=@SNO
            from   ProductBaseInfo
            where   pc=@pc   and   pm   like   @pm   and   ps   like   @ps
        end
        select   Result= 'Update   Success! '
        return
    end
end
else
begin
    select   Result= 'pmodel   or   pstyle   Error! '
    return
end
GO


------解决方案--------------------
CREATE proc P_AddSNO
@pc varchar(50), 变量名
@pm varchar(200)= '% ',初值为%
@ps varchar(50)= '% ',
@SNO varchar(50),
@pid varchar(50)= ' '
as
if Exists(select * from ProductBaseInfo where pc=@pc and pm like @pm and ps like @ps) --如果在表ProductBaseInfo 存在一条字段pc=变量@pc和字段pm 匹配变量@pm 和字段ps匹配变量@ps的记录的话就执行下面的begin
begin
if Exists(select * from Storage where pclass=@pc and StorageNo=@SNO and pmodel like @pm and pstyle like @ps and productID=@pid) --道理同上
begin --如果存在返回一个字符串 ' 'Storage Postion be exist '
select Result= 'Storage Postion be exist '
return
end
else --如果不存在
begin
if @pid <> ' ' --如果传进来的Pid不为空的话就添加一条数据
begin
Insert into Storage(pclass,pmodel,pstyle,StorageNo,productID)
values(@pc,@pm,@ps,@sno,@pid)
end
else --否则添加一条从ProductBaseInfo表中根据条件选出来的数据
begin
Insert into Storage(pclass,pmodel,pstyle,StorageNo)
select distinct pc,pm,ps,StorageNo=@SNO
from ProductBaseInfo
where pc=@pc and pm like @pm and ps like @ps
end
select Result= 'Update Success! ' --添加成功返回一个字符串表示成功
return
end
end
else
begin --出错
select Result= 'pmodel or pstyle Error! '
return
end
GO