请大家帮我看下

[liangjianshi]

最近在学写存储过程,好多东西不懂,还请大家多帮忙

create procedure chye
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin
declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)


select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
exec('select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc=''+@ckmc+''
select @zjiner=sum(hsje) from ['+@djieb+']
')
else
exec('select wpbh,ckmc,kcsl,bhsjer from ['+@djieb+'] where kcsl<>0 and ckmc=''+@ckmc+''
select @zjiner=sum(bhsjer) from ['+@djieb+']
')

end

GO

declare @zjiner varchar(200)
exec chye '集团','配件库',@zjiner output
print @zjiner

必须声明变量 '@zjiner'。

[xeqtrl982]

exec('select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc=''+@ckmc+''
select @zjiner=sum(hsje) from ['+@djieb+']
')
---------------------
exec -------------->用sp_executesql

[marco08]

--参考

use pubs
go
create proc pc
@tbName varchar(100),
@id int output
as
begin
declare @sql nvarchar(4000) --必须是nvarchar

set @sql='select @id=count(*) from ['+@tbName+']'

exec sp_executesql @sql, N'@id int output', @id output
end
go

declare @i int
exec pc 'titles', @i output
print @i

--result
18

[marco08]

--try

create procedure chye
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

declare @sql nvarchar(4000)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
@sql='select @zjiner=sum(hsje) from ['+@djieb+']'
else
@sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'


exec sp_executesql @sql, N'@zjiner varchar(200)', @zjiner output

end
GO

declare @zjiner varchar(200)
exec chye '集团','配件库',@zjiner output
print @zjiner

[david_anwei]

不明白什么意思

[liangjianshi]

marco08(天道酬勤)
第 18 行: '@sql' 附近有语法错误。

[liangjianshi]

@sql='select @zjiner=sum(hsje) from ['+@djieb+']'
是少了个set吧,但是我加上后运行
提示:必须声明变量 '@zjiner'。
不是已经声明了吗?

[marco08]

--sorry, 少了SET，还有我把你的一个SELECT语句去掉了，你自己加上去试试

if @ckmc='工程库'
set @sql='select @zjiner=sum(hsje) from ['+@djieb+']'
else
set @sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'

[marco08]

create procedure chye
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

declare @sql nvarchar(4000)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
set @sql='select @zjiner=sum(hsje) from ['+@djieb+']'
else
set @sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'


exec sp_executesql @sql, N'@zjiner varchar(200)', @zjiner output

end
GO


--OK，可以创建PROC

[liangjianshi]

不好意思,提示的这个错误

服务器: 消息 8162，级别 16，状态 2，行 0
形式参数 '@zjiner' 定义为 OUTPUT，但实际参数却未声明为 OUTPUT。

[marco08]

--sorry, 再改改

create procedure chye
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

declare @sql nvarchar(4000)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
set @sql='select @zjiner=sum(hsje) from ['+@djieb+']'
else
set @sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'


exec sp_executesql @sql, N'@zjiner varchar(200) output', @zjiner output

end
GO

[marco08]

liangjianshi(两件事) ( ) 信誉：100 Blog 2006-12-23 11:01:18 得分: 0


不好意思,提示的这个错误

服务器: 消息 8162，级别 16，状态 2，行 0
形式参数 '@zjiner' 定义为 OUTPUT，但实际参数却未声明为 OUTPUT。

---------------
exec sp_executesql @sql, N'@zjiner varchar(200) output', @zjiner output

因为这句少了一个output

[liangjianshi]

create procedure chye1
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

declare @sql nvarchar(4000)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc='+@ckmc+'
set @sql='select @zjiner=sum(hsje) from ['+@djieb+']'
else
select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc='+@ckmc+'
set @sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'


exec sp_executesql @sql, N'@zjiner varchar(200) output', @zjiner output

end
GO

declare @zjiner varchar(200)
exec chye1 '集团','配件库',@zjiner output
print @zjiner


在关键字 'else' 附近有语法错误。

[liangjianshi]

create procedure chye1
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

declare @sql nvarchar(4000)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
begin

select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc=@ckmc
set @sql='select @zjiner=sum(hsje) from ['+@djieb+']'
end
else
begin
select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc=@ckmc
set @sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'
end

exec sp_executesql @sql, N'@zjiner varchar(200) output', @zjiner output

end
GO

declare @zjiner varchar(200)
exec chye1 '集团','工程库',@zjiner output
print @zjiner

对象名 ''+@djieb+'' 无效。

[liangjianshi]

现在运行不提示错误了.@zjiner也能输出出来了,
可是查询的记录没出来,应该有的
create procedure chye
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

declare @sql nvarchar(4000)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

if @ckmc='工程库'
begin

exec('select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc=''+@ckmc+''')
set @sql='select @zjiner=sum(hsje) from ['+@djieb+']'
end
else
begin
exec('select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc=''+@ckmc+''')
set @sql='select @zjiner=sum(bhsjer) from ['+@djieb+']'
end

exec sp_executesql @sql, N'@zjiner varchar(200) output', @zjiner output

end
GO

declare @zjiner varchar(200)
exec chye '集团','工程库',@zjiner output
print @zjiner

（所影响的行数为 0 行）

84526.1

[marco08]

--try

create procedure chye
@gsmc varchar(200),
@ckmc varchar(200),
@zjiner varchar(200) output

as
begin

declare @zhangbiao as varchar(20)
declare @djieb as varchar(20)
declare @djb as varchar(20)

select @zhangbiao=zhangbiao,@djieb=djieb,@djb=djb from gongsibiao where gsmc=@gsmc

declare @sql nvarchar(4000)

set @sql='select wpbh,ckmc,kcsl,hsje from ['+@djieb+'] where kcsl<>0 and ckmc='''+@ckmc+''''

if @ckmc='工程库'
set @sql=@sql+' select @zjiner=sum(hsje) from ['+@djieb+']'
else
set @sql=@sql+' select @zjiner=sum(bhsjer) from ['+@djieb+']'

exec sp_executesql @sql, N'@zjiner varchar(200) output', @zjiner output

end
GO

[liangjianshi]

ckmc='''+@ckmc+''''
是这个地方错了.
太感谢marco08(天道酬勤) 了.