存储过程的问题

[liangjianshi]

<%
wp=request("wpmc")
xh=request("ggxh")

sql="select * from wpjbxx"
if wp<>"" then
sq1= sql & " and wp='"&wp&"'"
end if

if xh<>"" then
sq1= sql &" and xh='"&xh&"'"
end if

rs.open sql,conn,1,1
%>
把上面的用存储过程写出来
CREATE PROCEDURE wp_deatil1
( @mc varchar(50),
@xh varchar(50)
)
AS
Declare @sql varchar(6000)
set @sql = 'select * from wpjbxx where 1=1'
if @mc<>'' begin
set @sql = @sql + 'and wpmc='+ @mc
end
if @xh<>'' begin
set @sql = @sql + 'and ggxh='+ @xh
end

exec(@Sql)
GO

然后在程序中调用这个存储过程，可怎么弄都不行。
在程序中怎么调用存储过程啊，麻烦够手给写个调用上面的存储过程的代码吧。

[icefire988]

prc:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ICEFIRE
-- Create date: 2006-09-13
-- Description: test
-- =============================================
CREATE PROCEDURE test
-- Add the parameters for the stored procedure here
@mc varchar(50) = '',
@xh varchar(50) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql varchar(8000),
@condition varchar(500)
set @condioton = ''
if @mc <> ''
set @condition = ' and wpmc = ''' + @mc + ''''
if @xh <> ''
set @condition = @condition + ' and ggxh = ''' + @xh + ''''

-- Insert statements for procedure here
set @sql = 'select * from wpjbxx where 1=1 ' + @condition

--print(@sql)
exec(@sql)
END
GO

asp:
set ocmd = server.createobject("adodb.command")
set rs=server.createobject("adodb.recordset")

rs.cursorlocation=aduseserver

with ocmd

.commandtext="test"
.commandtype=adcmdstoredproc
.commandtimeout = 0
.activeconnection=dbcon

.parameters.append .createparameter ("@mc", advarchar, adparaminput, 50)
.parameters.append .createparameter ("@xh", advarchar, adparaminput, 50)

.parameters("@mc") = mc
.parameters("@xh") = xh

end with

rs.open ocmd,,adopenforwardonly,adlockreadonly

if not rs.bof and not rs.eof then
'处理过程
end if
rs.close
set rs = nothing

[liangjianshi]

CREATE PROCEDURE wp_deatil1
( @mc varchar(50),
@xh varchar(50)
)
AS
Declare @sql varchar(6000)
set @sql = 'select * from wpjbxx where 1=1'
if @mc<>'' begin
set @sql = @sql + 'and wpmc='+ @mc
end
if @xh<>'' begin
set @sql = @sql + 'and ggxh='+ @xh
end

exec(@Sql)
GO
这个对吗？

[icefire988]

不对,下班闪人.

[liangjianshi]

CREATE PROCEDURE wp_deatil1
@mc varchar(50) = '',
@xh varchar(50) = ''
AS
BEGIN

SET NOCOUNT ON;
declare @sql varchar(8000)
if @mc <> ''
set @sql =@sql + ' and wpmc = ''' + @mc + ''''
if @xh <> ''
set @sql =@sql + ' and ggxh = ''' + @xh + ''''
exec(@sql)
END
GO

[liangjianshi]

这样可以了。但是运行出问题了
<%
dim objCnn
dim objCmd
dim Rs
wpmc=request("wpmc")
ggxh=request("ggxh")

'-----建立Connection对象----------
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "Driver={SQL Server};Server=127.0.0.1;uid=ry;pwd=333;database=rykc;"
'-----建立Command对象-----------
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="wp_deatil1" '指定存储过程名称
objCmd.CommandType=4 '其为Stored Procedure
'-----准备stored procedure 的参数-------
objCmd.Parameters.Append objCmd.CreateParameter("@mc",advarchar,adparaminput,50,wpmc)
objCmd.Parameters.Append objCmd.CreateParameter("@xh",advarchar,adparaminput,50,ggxh)
'-----执行存储过程----------------------
objCmd.Execute

'-----输出参数以及处理结果--------------
for each parm in objCmd.Parameters
Response.Write parm.name &"="& trim(parm) &"<br>"
next
%>
提示：
参数类型不正确，或不在可以接受的范围之内，或与其他参数冲突。
/b1.asp, 第 17 行

[liangjianshi]

解决了：
CREATE PROCEDURE wp_deatil1
@mc varchar(50) = '',
@xh varchar(50) = ''
AS
BEGIN

declare @sql varchar(8000)
set @sql='select * from wpjbxx where 1=1'
if @mc <> ''
set @sql =@sql + ' and wpmc = ''' + @mc + ''''
if @xh <> ''
set @sql =@sql + ' and ggxh = ''' + @xh + ''''
exec(@sql)
END
GO


<%
dim objCnn
dim objCmd
dim Rs
wpmc=request("wpmc")
ggxh=request("ggxh")

'-----建立Connection对象----------
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "Driver={SQL Server};Server=127.0.0.1;uid=ry;pwd=333;database=rykc;"
'-----建立Command对象-----------
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="wp_deatil1" '指定存储过程名称
objCmd.CommandType=4 '其为Stored Procedure
'-----准备stored procedure 的参数-------
‘下面是17行
objCmd.Parameters.Append objCmd.CreateParameter("@mc",200,,50,wpmc)
objCmd.Parameters.Append objCmd.CreateParameter("@xh",200,,50,ggxh)
'-----执行存储过程----------------------
objCmd.Execute

'-----输出参数以及处理结果--------------
for each parm in objCmd.Parameters
Response.Write parm.name &"="& trim(parm) &"<br>"
next
%>


还有个问题我想问下，
objCmd.Parameters.Append objCmd.CreateParameter("@mc",200,1,50,wpmc)
为什么我用
objCmd.Parameters.Append objCmd.CreateParameter("@mc",advarchar,adparaminput,50,wpmc)
运行就提示错误了呢？
type，direction这两项到底用什么呢？
为什么type的地方换成了advarchar就不行了呢？
1换成adparaminput也不行。
望高手给解释一下。

运行提示：
ADODB.Command (0x800A0BB9)
参数类型不正确，或不在可以接受的范围之内，或与其他参数冲突。
/b1.asp, 第 17 行

[icefire988]

advarchar这些是前面include的adovbs.inc定义的常量.你那没有当然出错了.

[menong]

'--------------------------------------------------------------------
' Microsoft ADO
'
' Copyright (c) 1996-1998 Microsoft Corporation.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adPropVariant = 138
Const adVarNumeric = 139
Const adArray = &H2000