asp开发bs架构的三层系统,对于中小结构的系统把业务逻辑放在sqlserver的存储过程里面是一个很好的实现方法,但这么做出现了一个asp客户端和数据库存储过程之间配合调试的不方便.

实际开发中我写了下面一个函数,自动把asp端传给数据库的参数取出,自动生成符合sqlserver查询分析器的规则的存储过程执行命令串,放在textarea控件里,可以从页面直接复制到查询分析器里进行存储过程调试,下面就是程序.
‘==================================================================’
dim dsn     ‘数据源
dim storename    ‘存储过程名
dim con     ‘Connection对象
dim cmd     ‘Command对象
dim rs     ‘Recordset 对象
set con=server.createobject(“ADODB.Connection”)
con.open dsn
set cmd=server.createobject("ADODB.Command")
set cmd.ActiveConnection=Con
cmd.CommandText= storename
cmd.Commandtype=adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@State",adInteger,adParamInput,4)
cmd.Parameters.Append cmd.CreateParameter("@WhereT",adVarChar,adParamInput,200)
cmd.Parameters.Append cmd.CreateParameter("@BranchType",adVarChar,adParamInput,50)
cmd("@State") = 1
cmd("@WhereT")=”2”
cmd("@BranchType")=”3”
getpmt(cmd)      ‘获取存储过程的参数
set rs=server.CreateObject("adodb.recordset")
rs.CursorType =3
rs.LockType =3
rs.CursorLocation =3
set rs.Source =cmd
rs.Open
‘======================================================’
‘    形成存储过程的函数
‘=======================’
‘   输入参数 a= command对象
‘=======================’
sub getpmt(a)
    b=a.CommandText
 b=replace(b,"?","")
 b=replace(b,",","")
 b=replace(b,"}","")
 b=replace(b,"{","")
 b=replace(b," ","")
 b=replace(b,"call","")
 b=replace(b,"(","")
 b=replace(b,")","")
 Response.Write "<center><table border=1 cellpadding =0 cellspacing =0 bordercolordark=#ffffff bordercolorlight=#cccc88><tr onclick="&b&".style.display='><td bgcolor=#cccc88>" & b & "</td></tr><tr id="&b&" style=display:none><td>"
 Response.Write "<TEXTAREA rows=6 cols=70 style='font-color:#ccc888;bgcolor:#ffffc'>"
 Response.Write b
 for each element in a.parameters
  c=element
  c="'" & replace(c,"'","'") & "'"
  d=d &  vbcrlf &   element.name  &"="& c &","
 next
 d=left(d,len(d)-1)
Response.Write d
 Response.Write "</TEXTAREA></td></tr></table></center>"
end sub