Change stored proc to inline sql

Question

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?

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

Stored Procedure definition

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

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

the select * was a mistake

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
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

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!

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

Thanks everyone.

Why do you want to switch to dynamic SQL instead of a Stored Procedure?, your code is fine you are just [missing one line](http://stackoverflow.com/a/36127333/692942). — user692942, Mar 21 '16 at 09:54
I would side with Lankymart, here, Austin; no reason to change. — Paul, Mar 21 '16 at 11:41
With everyone's help I got all three methods working and created working examples. See the edit to my original post. tx everyone — Austin, May 09 '16 at 23:31

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

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

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 
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}

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

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.

score 1 · Answer 2 · edited Mar 21 '16 at 10:10

1

To switch from a stored procedure to inline SQL, you need to change

cmd.CommandType = adCmdStoredProc

to

cmd.CommandType = adCmdText

Then you need to add the query to the command text property:

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

The above line was derived from the Command Object Parameters example on MSDN.

edited Mar 21 '16 at 10:10

user692942

16,398
7
76
175

answered Mar 21 '16 at 01:00

mikek3332002

3,546
4
37
47

1

This will also work but why switch to dynamic SQL when the Stored Procedure works fine? – user692942 Mar 21 '16 at 09:55

Change stored proc to inline sql

2 Answers2

Useful Links

Linked