I have some time to start looking into the issue of moving my db-specific code out of ASP into stored procs, and one of the things I need to work out is how to handle the fact that a great deal of my coding is tied to the request.form and request call in ASP. For example, a standard code block i use for inserting values into a table from form submit goes like this:

<font face="Georgia">
ComplainantSubmitTime = Now()

dim InsertString

mySQL = "Set Nocount on "

for each item in Request.Form
Select Case item

Case "Submit Complainant Information"

Case "Return"

Case Else

InsertString = InsertString & item & ", "
End Select

InsertStringLength = len(InsertString)
InsertStringLengthTrim = InsertStringLength -2
InsertStringName = Left(InsertString, InsertStringLengthTrim)

mySQL = mySQL & "INSERT INTO dbo.Complainant_Info (" & InsertStringName & ""

mySQL = mySQL & ", ComplainantSubmitTime)"

dim ValueString

for each item in Request.Form
Select case item

Case "Submit Complainant Information"

Case "Return"

Case else

ValueString = ValueString & request.form(item) & "', '"

End Select

ValueStringLength = len(ValueString)
ValueStringLengthTrim = ValueStringLength -3
ValueStringName = Left(ValueString , ValueStringLengthTrim)
ValueStringName = "'" & ValueStringName

mySQL = mySQL & "VALUES (" & ValueStringName & ""

mySQL = mySQL & ", " & "'" & ComplainantSubmitTime & "'" & ")"
'response.write mySQL

mySQL = mySQL + " select IdentityInsert=Scope_Identity()"

set myRS = DataConnection.execute (mySQL)
</font face=georgia>
As you can see, I am polling the request.form objects passed from the form fields to build an INSERT statement. How do I make this happen in a stored proc? And, also note that this INSERT code is flexible -- that is, if you add/change/delete a form field, the INSERT statement will build as needed. So I can't setup hard-coded @input vars, which means some dynamic SQL to the db. I have a general strategy in my web/db development that allows easy maintenance of the form to the db: any change to the form requires a change to a target column in the db but the INSERT code never needs to be altered.

Any suggestions?