请教一个关于存储过程比较简单的问题！

[fhb2007]

我在数据库定义了下面的一个存储过程：
CREATE PROCEDURE f_yb0
@my_b1 varchar(20) ,
@jd1 int
AS
declare @str varchar(500)
set @str = ' select id from ' + @my_b1 + ' where cpdw_id=0 and jd='+@jd1
exec(@str)
GO

然后自定义一个函数调用该存储过程：
function f_yb(my_b1,flag,jd1)
my_b2=my_b1
jd2=jd1
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection=Connstr
MyComm.CommandText="f_yb0"
MyComm.CommandType=4
MyComm.Prepared=true
MyComm.Parameters.append MyComm.CreateParameter("@my_b1",200,1,40,my_b2)
MyComm.Parameters.append MyComm.CreateParameter("@jd1",3,1,4,jd2)
set rs1=MyComm.Execute '74行
f_yb=rs1.recordcount
set rs1=nothing
set MyComm=nothing
end function
调用的时候在74行出错，出错内容：
[Microsoft][ODBC SQL Server Driver][SQL Server]将 varchar 值 ' select id from cp_jianshe where cpdw_id=0 and jd=' 转换为数据类型为 int 的列时发生语法错误。
请问应该如何改？

[hookee]

试试看

CREATE PROCEDURE f_yb0 
 @my_b1 varchar(20), 
 @jd1 int 
AS
set nocount on
declare @str varchar(500) 
set @str = ' select id from ' + @my_b1 + ' where cpdw_id=0 and jd='+ CAST(@jd1 AS VARCHAR) 
execute(@str) 
GO 

[lzp4881]

存储过程中，当用字符串拼接时，只能用varchar，所以你的@jd1 int 可以应该改为@jd1 varchar(10)，相应的调用程序也改一下。

[fhb2007]

2楼的老兄，你改写的存储过程直行起来出错啊！

[hookee]

以下代码测试通过，环境是 sql20005 SQLCLI连接。

CREATE PROCEDURE f_yb0 
 @my_b1 varchar(20), 
 @jd1 int 
AS
set nocount on
declare @str varchar(500) 
set @str = ' select id from ' + @my_b1 + ' where cpdw_id=0 and jd='+ CAST(@jd1 AS VARCHAR) 
execute(@str) 
GO 

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn

jd1=5
my_b1="users"

Set oCmd    = CreateObject("ADODB.Command")
With oCmd
    .CommandText = "f_yb0"
    .CommandType = 4
    Set oPara = .CreateParameter("@dF", 200, 1, 20, my_b1)
    .Parameters.Append oPara
    Set oPara = .CreateParameter("@dF", 3, 1, 4, jd1)
    .Parameters.Append oPara
    Set .ActiveConnection = oConn
    Set rs = .Execute()
    Set .ActiveConnection = Nothing
End With
Do While Not rs.EOF
  WScript.Echo rs(0)
   rs.MoveNext
Loop 
With oConn
    If .State <>0 Then .Close
End With
Set oCmd = Nothing
Set oConn = Nothing

[fhb2007]

五楼的朋友，你下面的语句是什么意思?
Do While Not rs.EOF
WScript.Echo rs(0)
rs.MoveNext
Loop
With oConn
If .State <>0 Then .Close
End With

[hookee]

Do While Not rs.EOF
Response.Write rs(0) '输出记录第一个字段
rs.MoveNext
Loop
'关闭连接
With oConn
If .State <>0 Then .Close
End With