Измените сохраненный процесс на встроенный SQL

Question

У меня работает следующий классический asp, вызывающий сохраненный процесс в базе данных. Я хотел бы преобразовать это так, вместо того, чтобы вызывать хранимый процесс, который он передает в SQL, добавляет коляски, а затем выполняет его.

Я перепробовал все виды вещей и не могу заставить его работать. Может кто-то преобразовать этот пример?

Также кажется трудным получить разумные сообщения об ошибках при переходе в sql с параметрами. Есть ли способ увидеть следы этого вызова, чтобы мы могли иметь некоторое представление о том, что вызывает проблему?

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

РЕДАКТИРОВАТЬ:
Вот мои ответы всем.
Wow Stackru это здорово, потому что все, как вы
которые проводят немного времени, помогая другим.

выбор * был ошибкой

Я должен поддерживать и преобразовывать некоторые старые коды ASP для использования сохраненных процедур.
Хранимые процедуры - это путь к "большей части" времени.
По разным причинам иногда лучше иметь sql в коде.
(быстрое тестирование и разработка, отсутствие доступа к базе данных и т. д.)
Поэтому мне нужно знать, как справиться с обоими способами.

cmd.Parameters.Refresh
Мой код прекрасно работает без этого вызова.
Это действительно необходимо?
Чтение того, что он должен делать, не было большой помощью, зачем мне это нужно

Понимание типов имеет решающее значение для всех типов программирования.
Это было именно то, что я просил, и даже больше.
Карл Протман - Отображение типов данных
Спасибо за это!

Мне также было интересно, как установить объект набора записей, хотя я забыл спросить. Спасибо за это тоже!
set rs = server.createObject ("adodb.recordset")
rs = - cmd.Execute

Я получил все три работы. Для всех интересующихся здесь работает и протестирован код, демонстрирующий все три подхода.

' 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

Спасибо всем.

Answer 1

Нет ничего плохого в приведенном выше коде, который вы просто пропускаете, используя Refresh() метод Parameters Коллекция, прежде чем пытаться установить значения именованных параметров.

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

Если вы не хотите использовать этот метод, определения параметров должны происходить откуда-то, поэтому другой вариант - определить их самостоятельно, чтобы отразить определения хранимой процедуры.

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 - отображение типов данных

Примечание: у вас есть эта строка в вашей хранимой процедуре;

select * from EmailResponse 

Который ожидает вернуть набор результатов, но вы указываете adExecuteNoRecords в вашем ADODB.CommandExecute() метод, который заставляет это игнорироваться, если вы хотите вернуть его, настройте вышеприведенное значение на;

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

... ^{используется, чтобы показать, где код пропущен}

---

Необходимо отметить, что хотя подход DimaSUN (поскольку удаленный, не уверен, почему...) является надежным, он усложняет ситуацию, добавляя два дополнительных параметра, когда они не нужны, вы можете просто объявить параметры внутри динамического SQL и назначить их вместо этого использовать эти локально объявленные переменные для запуска операторов.

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 error '800a0e7c' Объект параметра неправильно определен. Была предоставлена ​​непоследовательная или неполная информация - некоторая информация об определении вручную Parameter чтобы избежать ошибок.

Answer 2

Чтобы переключиться с хранимой процедуры на встроенный SQL, вам нужно изменить

cmd.CommandType = adCmdStoredProc

в

cmd.CommandType = adCmdText

Затем вам нужно добавить запрос в текстовое свойство команды:

cmd.CommandText = "SELECT * FROM Orders WHERE CustomerID = ?"

Приведенная выше строка была получена из примера "Параметры объекта команды" в MSDN.