SQLstring Parcing Routine For Easier Reading Of Queries

I use this routine all the time to make large queries for inserts and updates easier to read and also to create.

the first variable beign passed is whether or not to use the insert(”i”) or update(”u”) portion of the code.
second variable passed is the column name
The third is the value being set
the fourth is whether the variable is to be treated as an integer or a string(true/false) true will surround the var in quotes, false will not.

<%Dim InsertSQL1, InsertSQL2, UpdateSQL1
Private Sub SmartSQL(vAction, vcolumn, vValue, vUsequotes)

vAction = lcase(vAction)
Dim vRealValue
vRealValue = vValue
If vUseQuotes then
vRealValue = “‘” & vValue & “‘”
End IF
Select CASE vAction
CASE insert string

If InsertSQL1=”” then
InsertSQL1 = “[” & vColumn & “]”
InsertSQL2 = vRealValue
Else
InsertSQL1 = InsertSQL1 & “, [” & vColumn & “]”
InsertSQL2 = InsertSQL2 & “, ” & vRealValue
End If

CASE “u”‘build update string

If UpdateSQL1=”” then
UpdateSQL1 = “[” & vColumn & “]” &”=”& vRealValue
Else
UpdateSQL1 = UpdateSQL1 & “, [” & vColumn & “]=” & vRealValue
End If

End Select

End Sub%>

Usage:

for an insert query

<%InsertSQL1 = “”
InsertSQL2 = “”

Call “userid”, false)
Call “name”, true)
Call “desc”, true)
Call “date”, true)

SQLstring = “Insert into [USERS] (” & InsrtSQL1 & “) values(” & InsertSQL2 & “);”
for an update query

<%‘Call “userid”, false)
Call “name”, true)
Call “desc”, true)
Call “date”, true)

SQLstring = “update [USERS] set ” & UpdateSQL1 & ” Where userid=” & & “;”