将存储过程更改为内联 sql [英] Change stored proc to inline sql

问题描述

我有以下经典的 asp 工作，调用数据库中的存储过程.我想将它转换过来，而不是调用它在 sql 中传递的存储过程，添加婴儿车然后执行它.

I have the following classic asp working, calling a stored proc in the database. I would like to convert it over so instead of calling the stored proc it passes in the sql, adds the prams then executes it.

我尝试了各种方法，但都无法正常工作.有人可以转换这个例子吗?

I tried all sorts of things and can't get it working. Can someone convert this example?

在使用参数传递 sql 时似乎很难得到合理的错误消息.有没有办法查看此调用的痕迹，以便我们了解导致问题的原因?

Also it seems difficult to get reasonable error messages when passing in sql with params. Is there a way to see traces of this call so we can have some idea what is causing the problem?

Set cmd = server.createobject("ADODB.Command")
cmd.ActiveConnection = theDatabase
cmd.CommandType = adCmdStoredProc
cmd.commandText = "AddResponse"

cmd.Parameters("@id") = id
cmd.Parameters("@pc") = pc
cmd.Parameters("@idDate") = now

cmd.Execute , , adExecuteNoRecords
set cmd = Nothing

存储过程定义

ALTER PROCEDURE [dbo].[AddResponse] @id NVARCHAR(12), @pc NVARCHAR(12), @idDate  datetime
AS
BEGIN
    SET NOCOUNT ON;
    select * from EmailResponse 
    if not exists (SELECT id, projectCode FROM EmailResponse WHERE id = @id and projectCode = @pc)
    begin 
        INSERT INTO EmailResponse (id, projectCode, dateEntered) VALUEs(@id, @pc, @idDate) 
    end
END

这是我给大家的答案.
哇，stackoverflow 很棒，因为每个人都和自己一样
花一点时间帮助别人的人.

Here are my answers to everyone.
Wow stackoverflow is great because of everyone like yourselves
who spend a little time helping others.

选择 * 是一个错误

我必须维护一些旧的 asp 代码并将其转换为使用存储过程.
存储过程是大多数"时间的方式.
由于各种原因，有时最好在代码中包含 sql.
(快速测试开发，无法访问数据库等)
所以我需要知道如何处理这两种方式.

I have to maintain and convert some older asp code over to using stored procs.
Stored proc are the way to go "most" of the time.
For various reasons sometimes it is better to have the sql in the code.
(quick testing and development, no access to the database, etc.)
So I need to know how to handle both ways.

cmd.Parameters.Refresh
如果没有这个调用，我的代码可以正常工作.
真的有必要吗?
阅读它应该做什么对我为什么需要使用它没有多大帮助

cmd.Parameters.Refresh
My code works fine without this call.
It is really necessary?
Reading what it is supposed to do was not a lot of help why I need to use it

了解类型对于所有类型的编程都至关重要.
这正是我所要求的以及更多.
Carl Prothman - 数据类型映射
谢谢你！

Understanding types is critical for all types of programming.
This was exactly what I was asking for and more.
Carl Prothman - Data Type Mapping
Thanks for this!

我也想知道如何设置记录集对象，即使我忘了问.也谢谢你！
设置 rs = server.createObject ("adodb.recordset")
rs =- cmd.Execute

I was also wondering how to set a record set object even though I forgot to ask. Thanks for this too!
set rs = server.createObject ("adodb.recordset")
rs =- cmd.Execute

我得到了所有三个工作.对于任何对此感兴趣的人来说，正在工作和测试代码以显示所有三种方法.

I got all three working. For anyone interested here is working and tested code to show all three approaches.

' Stored proc example
' ------------------------------------------
dim theDatabase, cmd, id, pc
theDatabase    = "Driver={SQL Server}; Server=10.10.10.10,1433; Database=Test; uid=TestUser; pwd=TestPass;"

id = cleanInt(request.querystring("id"))
pc = sqlSafe(clean(request.querystring("pc")))

if pc<>"" and id<>"" then
    Set cmd = server.createobject("ADODB.Command")

    cmd.ActiveConnection = theDatabase
    cmd.CommandType      = adCmdStoredProc
    cmd.commandText      = "AddResponse"

    cmd.Parameters("@id")     = id
    cmd.Parameters("@pc")     = pc
    cmd.Parameters("@idDate") = now

    cmd.Execute , , adExecuteNoRecords
    set cmd = Nothing
end if


' Inline SQl with ? example
' ------------------------------------------
dim theDatabase, cmd, id, pc
theDatabase    = "Driver={SQL Server}; Server=10.10.10.10,1433; Database=Test; uid=TestUser; pwd=TestPass;"

id = cleanInt(request.querystring("id"))
pc = sqlSafe(clean(request.querystring("pc")))

if pc<>"" and id<>"" then
    Set cmd = server.createobject("ADODB.Command")

    cmd.ActiveConnection = theDatabase
    cmd.CommandType      = adCmdText
    cmd.CommandText      = _
        "if not exists (SELECT id, projectCode FROM EmailResponse WHERE id = ? and projectCode = ?)" &_
        "begin INSERT INTO EmailResponse (id, projectCode, dateEntered) VALUEs(?, ?, ?) end "

    cmd.Parameters.Append cmd.CreateParameter("@id",     adInteger,     adParamInput,   ,  id)
    cmd.Parameters.Append cmd.CreateParameter("@pc",     adVarchar,     adParamInput, 12,  pc)
    cmd.Parameters.Append cmd.CreateParameter("@id2",    adInteger,     adParamInput,   ,  id)
    cmd.Parameters.Append cmd.CreateParameter("@pc2",    adVarchar,     adParamInput, 12,  pc)        
    cmd.Parameters.Append cmd.CreateParameter("@idDate", adDBTimeStamp, adParamInput, -1,  now)

    cmd.Execute , , adExecuteNoRecords
    set cmd = Nothing
end if

' Inline SQl with @ example
' ------------------------------------------
dim theDatabase, cmd, sql, id, pc
theDatabase    = "Driver={SQL Server}; Server=10.10.10.10,1433; Database=Test; uid=TestUser; pwd=TestPass;"

id = cleanInt(request.querystring("id"))
pc = sqlSafe(clean(request.querystring("pc")))

if pc<>"" and id<>"" then
    Set cmd = server.createobject("ADODB.Command")

    sql = ""
    sql = sql & "SET NOCOUNT ON;" & vbCrLf
    sql = sql & "DECLARE @id NVARCHAR(12)" & vbCrLf
    sql = sql & "DECLARE @pc NVARCHAR(12)" & vbCrLf
    sql = sql & "DECLARE @idDate DATETIME" & vbCrLf
    sql = sql & "SELECT @id = ?, @pc = ?, @idDate = ?" & vbCrLf
    sql = sql & "IF NOT EXISTS (SELECT id, projectCode FROM EmailResponse WHERE id = @id and projectCode = @pc)" & vbCrLf
    sql = sql & "INSERT INTO EmailResponse (id, projectCode, dateEntered) VALUEs(@id, @pc, @idDate);"

    cmd.ActiveConnection = theDatabase
    cmd.CommandType      = adCmdText
    cmd.CommandText      = sql
    cmd.Prepared = true
    cmd.Parameters.Append cmd.CreateParameter("@id",     adInteger,     adParamInput,   ,  id)
    cmd.Parameters.Append cmd.CreateParameter("@pc",     adVarchar,     adParamInput, 12,  pc)
    cmd.Parameters.Append cmd.CreateParameter("@idDate", adDBTimeStamp, adParamInput, -1,  now)

    cmd.Execute , , adExecuteNoRecords
    set cmd = Nothing
end if

谢谢大家.

推荐答案

上面的代码没有错，你只是使用 Refresh() method 的 Parameters 集合，然后再尝试设置命名参数值.

There is nothing wrong with the above code you are just missing using the Refresh() method of the Parameters collection before trying to set the named parameter values.

Set cmd = server.createobject("ADODB.Command")
With
  .ActiveConnection = theDatabase
  .CommandType = adCmdStoredProc
  .commandText = "AddResponse"

  'Query the provider for the parameter details
  Call .Parameters.Refresh()

  .Parameters("@id") = id
  .Parameters("@pc") = pc
  .Parameters("@idDate") = now

  Call .Execute(, , adExecuteNoRecords)
End With
set cmd = Nothing

如果您不想使用此方法，则参数定义必须来自某个地方，因此另一种选择是自己定义它们以反映存储过程的定义.

If you don't want to use this method the parameter definitions have to come from somewhere so the other option is to define them yourself to reflect the definitions of the stored procedure.

Set cmd = server.createobject("ADODB.Command")
With cmd
  .ActiveConnection = theDatabase
  .CommandType = adCmdStoredProc
  .commandText = "AddResponse"

  'Define parameters manually
  Call .Parameters.Append(.CreateParameter("@id", adVarWChar, adParamInput, 12))
  Call .Parameters.Append(.CreateParameter("@pc", adVarWChar, adParamInput, 12))
  Call .Parameters.Append(.CreateParameter("@idDate", adDBTimeStamp, adParamInput, 8))

  .Parameters("@id") = id
  .Parameters("@pc") = pc
  .Parameters("@idDate") = now

  Call .Execute(, , adExecuteNoRecords)
End With
set cmd = Nothing

如果您确实遵循手动路线，这是一个很好的资源，用于确定 ADO DataTypeEnum 使用的常量是 Carl Prothman - 数据类型映射

If you do go down the manual route a great resource for identifying what ADO DataTypeEnum constants to use is Carl Prothman - Data Type Mapping

旁注:您的存储过程中有这一行；

Side-note: You have this line in your Stored Procedure;

select * from EmailResponse 

希望返回结果集，但您在 ADODB.Command Execute() 方法中指定 adExecuteNoRecords 会导致此结果被忽略，如果您确实想返回它，请将上面的内容调整为；

Which expects to return a resultset but you specify adExecuteNoRecords in your ADODB.Command Execute() method which causes this to be ignored, if you do want to return it adjust the above to be;

Dim rs
...
With cmd
  ...
  Set rs = .Execute()
End With

^{... 用于显示代码被省略的地方}

^{... is used to show where code is omitted}

---

需要指出的是，虽然 @dimason 方法 (因为被移除了，不知道为什么......) 是合理的，它通过在不需要它们时添加两个额外的参数来使事情变得过于复杂，你可以声明动态 SQL 中的参数并分配它们以使用那些本地声明的变量来运行语句.

---

Needs pointing out that while @dimason approach (since removed, not sure why...) is sound it does over complicate things by adding two extra parameters when they are not needed, you can just declare the parameters inside the dynamic SQL and assign them to use those locally declared variables to run the statements instead.

Dim sql

sql = ""
sql = sql & "SET NOCOUNT ON;" & vbCrLf
sql = sql & "DECLARE @id NVARCHAR(12)" & vbCrLf
sql = sql & "DECLARE @pc NVARCHAR(12)" & vbCrLf
sql = sql & "DECLARE @idDate DATETIME" & vbCrLf
sql = sql & "SELECT @id = ?, @pc = ?, @idDate = ?" & vbCrLf
sql = sql & "SELECT * FROM EmailResponse;" & vbCrLf
sql = sql & "IF NOT EXISTS (SELECT id, projectCode FROM EmailResponse WHERE id = @id and projectCode = @pc)" & vbCrLf
sql = sql & "INSERT INTO EmailResponse (id, projectCode, dateEntered) VALUEs(@id, @pc, @idDate);"

Set cmd = server.createobject("ADODB.Command")
With cmd
  .ActiveConnection = theDatabase
  .CommandType = adCmdText
  .CommandText = sql
  .Prepared = true
  .Parameters.Append cmd.CreateParameter("@id", adVarChar, adParamInput, 12, id)
  .Parameters.Append cmd.CreateParameter("@pc", adVarChar, adParamInput, 12, pc)
  .Parameters.Append cmd.CreateParameter("@idDate", adDBTimeStamp,  adParamInput, 8, Now())
  Set rsOut = .Execute()
End With
Set cmd = Nothing

---

有用的链接

• 回答 ADODB.Parameters 错误 '800a0e7c' 参数对象定义不正确.提供的信息不一致或不完整 - 一些关于手动定义参数以避免错误的信息.

---

Useful Links

• Answer to ADODB.Parameters error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided - Some information about manually defining a Parameter to avoid errors.

这篇关于将存储过程更改为内联 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！