存储过程调用存储过程（严重问题）

[cnh03402]

ALTER procedure prc_getWBcheckCount
@wb_lot_no varchar(50),
@wb_mach_no varchar(50),
@wb_checkCount int output
AS
begin
declare @sql varchar(500)
set @sql = ' select COUNT(*) from wb_master '
set @sql = @sql +' WHERE wb_lot_no like ''%'+@wb_lot_no+'%'''
set @sql = @sql +' AND wb_mach_no like ''%'+@wb_mach_no+'%'''
exec(@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

我怎样用我外面定义的@wb_checkCount来接收count(*)值？
请指教，我需要在另外一个存储过程调用这个存储过程
在线等，急。。。。。

[永生天地]

ALTER procedure prc_getWBcheckCount
@wb_lot_no varchar(50),
@wb_mach_no varchar(50),
@wb_checkCount int output
AS
begin
declare @sql nvarchar(500),@cnt int
set @sql = ' select COUNT(*) from wb_master '
set @sql = @sql +' WHERE wb_lot_no like ''%'+@wb_lot_no+'%'''
set @sql = @sql +' AND wb_mach_no like ''%'+@wb_mach_no+'%'''
exec sp_executesql @sql,N'@cnt int output',@cnt output
end
GO
SET QUOTED_IDENTIFIER OFF  
GO
SET ANSI_NULLS ON  
GO

[永生天地]

sql server的动态语句用法
http://blog.csdn.net/xys_777/archive/2010/06/24/5690909.aspx

[永生天地]

ALTER procedure prc_getWBcheckCount
@wb_lot_no varchar(50),
@wb_mach_no varchar(50),
@wb_checkCount int output
AS
begin
declare @sql nvarchar(500)
set @sql = ' select @cnt = COUNT(*) from wb_master '
set @sql = @sql +' WHERE wb_lot_no like ''%'+@wb_lot_no+'%'''
set @sql = @sql +' AND wb_mach_no like ''%'+@wb_mach_no+'%'''
exec sp_executesql @sql,N'@cnt int output',@wb_checkCount output
end
GO
SET QUOTED_IDENTIFIER OFF  
GO
SET ANSI_NULLS ON  
GO

修改一下

[SQL77]

ALTER procedure prc_getWBcheckCount
@wb_lot_no Nvarchar(50),
@wb_mach_no Nvarchar(50),
@wb_checkCount int output
AS
begin
select @wb_checkCount=COUNT(*) from wb_master 
 WHERE wb_lot_no like '%'+@wb_lot_no+'%' AND wb_mach_no like '%'+@wb_mach_no+'%'

end
GO
SET QUOTED_IDENTIFIER OFF  
GO
SET ANSI_NULLS ON  
GO
DECLARE @Rnt INT 
EXEC prc_getWBcheckCount 'A','B',@Rnt OUTPUT

SELECT @Rnt

[cnh03402]

谢谢你，我看一下

[feixianxxx]

ALTER procedure prc_getWBcheckCount
@wb_lot_no nvarchar(50),
@wb_mach_no nvarchar(50),
@wb_checkCount int output
AS
begin
declare @sql nvarchar(500),@c int
set @sql = N' select @count=COUNT(*) from wb_master '
set @sql = @sql +N' WHERE wb_lot_no like ''%'+@wb_lot_no+N'%'''
set @sql = @sql +N' AND wb_mach_no like ''%'+@wb_mach_no+N'%'''
exec sp_executesql @sql,
				N'@wb_lot_no nvarchar(50),@wb_mach_no nvarchar(50),@count int output',
				@wb_lot_no,@wb_mach_no,@count=@c output
set @wb_checkCount=@c
end
GO
SET QUOTED_IDENTIFIER OFF  
GO
SET ANSI_NULLS ON  
GO

[feixianxxx]

[Quote=引用 4 楼 sql77 的回复:]

SQL code
ALTER procedure prc_getWBcheckCount
@wb_lot_no Nvarchar(50),
@wb_mach_no Nvarchar(50),
@wb_checkCount int output
AS
begin
select @wb_checkCount=COUNT(*) from wb_master
WHERE wb_lot_no lik……
[/Quote]
囧 ~~~ 直接拼接 ~~ LZ用这个

[cnh03402]

[Quote=引用 3 楼 xys_777 的回复:]

SQL code
ALTER procedure prc_getWBcheckCount
@wb_lot_no varchar(50),
@wb_mach_no varchar(50),
@wb_checkCount int output
AS
begin
declare @sql nvarchar(500)
set @sql = ' select @cnt = COUNT(*) from w……
[/Quote]

我在另外个存储过程，未调用到上面返回的行数值。有问题，未解决哈
我在另外个存储过程调用的方法是：
declare @count int
declare @sql nvarchar(500)
set @count=0
EXEC prc_getWBcheckCount @wb_lot_no,@wb_mach_no,@count output
select '-------------'
select @count

select @count查出来还是0

[cnh03402]

[Quote=引用 4 楼 sql77 的回复:]

SQL code
ALTER procedure prc_getWBcheckCount
@wb_lot_no Nvarchar(50),
@wb_mach_no Nvarchar(50),
@wb_checkCount int output
AS
begin
select @wb_checkCount=COUNT(*) from wb_master
WHERE wb_lot_no lik……
[/Quote]

你好。用你的方法，我的问题解决了。在存储过程2可以调用到存储过程1的值了。谢谢
我主要是用count与like,格式老有问题。谢谢了。问题解决了