我是楼主,楼上的解答错误,我原来使用的是"=",需要精确比较,代码中的"like"应该是"=" 我在查询分析器中执行 declare @bscid nvarchar(50), @yf nvarchar(10), @hbmc nvarchar(50), @shzt tinyint, @shyj nvarchar(200) set @bscid=001 set @yf=20031 select @hbmc=hbmc,@shzt=shzt,@shyj=shyj from bus_ys_doc where bscid=@bscid and yf=@yf; print @hbmc; print @shzt; print @shyj; 返回值为: 美元 1 ertretfdg但是执行以下语句时: declare @bscid nvarchar(50), @yf nvarchar(10), @hbmc nvarchar(50), @shzt tinyint, @shyj nvarchar(200) set @bscid=001 set @yf=20031 exec select_insert_bus_ys @bscid,@yf,@hbmc,@shzt,@shyj; print @hbmc; print @shzt; print @shyj; 返回值为空。 请高人解释。
use sp_executesql to return value
sp_executesql可以执行存储过程吗,楼上的说明白些
declare @sql nvarchar(1000) set @sql=N'select @hbmc=hbmc,@shzt=shzt,@shyj=shyj from bus_ys_doc where bscid like @bscid and yf like @yf' exec sp_executesql @sql,N' @hbmc nvarchar(50) output, @shzt tinyint output, @shyj nvarchar(200) output,@bscid nvarchar(50),@yf nvarchar10)',@hbmc output,@shzt output,@shyj output,@bscid,@yf CREATE procedure select_insert_bus_ys @bscid nvarchar(50), @yf nvarchar(10), @hbmc nvarchar(50) output, @shzt tinyint output, @shyj nvarchar(200) output as declare @ysdocid int declare @sql nvarchar(1000) set nocount on set @ysdocid = (select count(*) from bus_ys_doc)+1 if exists(select * from bus_ys_doc where bscid like @bscid and yf like @yf) begin
set @sql=N'select @hbmc=hbmc,@shzt=shzt,@shyj=shyj from bus_ys_doc where bscid like @bscid and yf like @yf' exec sp_executesql @sql,N' @hbmc nvarchar(50) output, @shzt tinyint output, @shyj nvarchar(200) output,@bscid nvarchar(50),@yf nvarchar10)',@hbmc output,@shzt output,@shyj output,@bscid,@yf select * from bus_ys_dtl where ysdocid=(select ysdocid from bus_ys_doc where bscid like @bscid and yf like @yf) end else begin if cast(@yf as int)-(select top 1 yf from bus_ys_doc order by yf desc)=1 or not exists(select yf from bus_ys_doc) begin insert into bus_ys_doc(ysdocid,yf,bscid) values (@ysdocid,@yf,@bscid) insert into bus_ys_dtl(ysdocid,kmid,kmmc) select ysdocid,kmid,kmmc from bus_ys_doc,pub_cwkm where ysdocid=@ysdocid select * from bus_ys_dtl where ysdocid=(select ysdocid from bus_ys_doc where bscid=@bscid and yf=@yf) end end GO
我在查询分析器中执行
declare @bscid nvarchar(50),
@yf nvarchar(10),
@hbmc nvarchar(50),
@shzt tinyint,
@shyj nvarchar(200)
set @bscid=001
set @yf=20031
select @hbmc=hbmc,@shzt=shzt,@shyj=shyj from bus_ys_doc where bscid=@bscid and yf=@yf;
print @hbmc;
print @shzt;
print @shyj;
返回值为:
美元
1
ertretfdg但是执行以下语句时:
declare @bscid nvarchar(50),
@yf nvarchar(10),
@hbmc nvarchar(50),
@shzt tinyint,
@shyj nvarchar(200)
set @bscid=001
set @yf=20031
exec select_insert_bus_ys @bscid,@yf,@hbmc,@shzt,@shyj;
print @hbmc;
print @shzt;
print @shyj;
返回值为空。
请高人解释。
set @sql=N'select @hbmc=hbmc,@shzt=shzt,@shyj=shyj from bus_ys_doc where bscid like @bscid and yf like @yf'
exec sp_executesql @sql,N' @hbmc nvarchar(50) output, @shzt tinyint output, @shyj nvarchar(200) output,@bscid nvarchar(50),@yf nvarchar10)',@hbmc output,@shzt output,@shyj output,@bscid,@yf
CREATE procedure select_insert_bus_ys
@bscid nvarchar(50),
@yf nvarchar(10),
@hbmc nvarchar(50) output,
@shzt tinyint output,
@shyj nvarchar(200) output
as
declare @ysdocid int
declare @sql nvarchar(1000)
set nocount on
set @ysdocid = (select count(*) from bus_ys_doc)+1
if exists(select * from bus_ys_doc where bscid like @bscid and yf like @yf)
begin
set @sql=N'select @hbmc=hbmc,@shzt=shzt,@shyj=shyj from bus_ys_doc where bscid like @bscid and yf like @yf'
exec sp_executesql @sql,N' @hbmc nvarchar(50) output, @shzt tinyint output, @shyj nvarchar(200) output,@bscid nvarchar(50),@yf nvarchar10)',@hbmc output,@shzt output,@shyj output,@bscid,@yf select * from bus_ys_dtl where ysdocid=(select ysdocid from bus_ys_doc where bscid like @bscid and yf like @yf)
end
else
begin
if cast(@yf as int)-(select top 1 yf from bus_ys_doc order by yf desc)=1 or not exists(select yf from bus_ys_doc)
begin
insert into bus_ys_doc(ysdocid,yf,bscid) values (@ysdocid,@yf,@bscid)
insert into bus_ys_dtl(ysdocid,kmid,kmmc) select ysdocid,kmid,kmmc from bus_ys_doc,pub_cwkm where ysdocid=@ysdocid
select * from bus_ys_dtl where ysdocid=(select ysdocid from bus_ys_doc where bscid=@bscid and yf=@yf)
end
end
GO