Though they take a bit more time to develop upfront, using database stored procedures are definitely the way to go for most information systems. They provide a looser coupling to the "data layer" by limiting the points of entry and offer stronger cohesion by breaking operations into reusable functionality. That said, I'm guessing that Jon's predecessor was continually reminded with "you should put that in a stored procedure" but didn't quite understand that he could create more than one of them ...

Public Function GetStoredProcCommand() As ADODB.Command

  Dim sqlCmd As ADODB.Command, param As ADODB.Parameter

  Set sqlCmd = New ADODB.Command
  sqlCmd.CommandType = adStoredProc
  sqlCmd.CommandText = "PROC__INSERT_OR_UPDATE_DATA"



' ED: This paramter tells the Überprocedure what to do
' it is an integer that's the primary key to the
' "stored_proc_actions" table, which contains only
' the "action_id" and the "action_name" columns param = sqlCmd.CreateParameter("action_to_perform", adInteger, adParamInput, 8, Null) sqlCmd.Parameters.Append param param = sqlCmd.CreateParameter("int_01_in", adInteger, adParamInput, 8, Null) sqlCmd.Parameters.Append param param = sqlCmd.CreateParameter("int_02_in", adInteger, adParamInput, 8, Null) sqlCmd.Parameters.Append param ' ED: Snipped 112 params param = sqlCmd.CreateParameter("vc50_19_in", adVarChar, adParamInput, 50, Null) sqlCmd.Parameters.Append param param = sqlCmd.CreateParameter("vc250_20_in", adVarChar, adParamInput, 250, Null) sqlCmd.Parameters.Append param ' ED: Snipped 43 params param = sqlCmd.CreateParameter("int_03_out", adInteger, adParamInputOutput, 8, Null) sqlCmd.Parameters.Append param 'return the command Set GetStoredProcCommand = sqlCmd End Function
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!