Using Stored procedure from VB with a UDT (User defined type) parameter

• Moshe Eshel

  SSC Enthusiast

  Points: 184

  More actions

  February 13, 2005 at 6:51 am

  #87522

  Hi,

  We've been looking into using UDT in the database level to reduce needs for change - defining a UDT and using it in tables/SP etc. so if a change is made to the field definition, we don't need to modify all the database objects but only the UDT itself.

  The only problem we found with this is that using the command object from VB 6 does not allowed speicifying the UDT as the param type, so that is another point of change.

  Since we do use VB for stored procedure execution, this causes a problem for the concept, since we will have to use the real type definition (i.e. nvarchar(80)) for the call to the SP from VB DLL, we will still have to find all SP using this type and modify them...

  We were discussing several alternatives to this (using Constants in the application which will reduce the change to only two for each change (DB level + VB Level)

  Does anyone here have any experience with this? does this seem like a good course of action? how would such a setup affect performance (UDT vs. Native definition)?

  Thanks

  Moshe Eshel

  meshel@gmail.com

• lionfan91

  SSCrazy Eights

  Points: 8794

  More actions

  February 14, 2005 at 7:08 am

  #542038

  Why use a datatype with the Command object at all?  Below is an example of how I use the Command object all the time:

  With cmd

     .ActiveConnection = myConn

     .CommandType = adCmdStoredProc

     .CommandText = "procMyProcedure"

     .Parameters("@Param1") = myParamOne

     .Parameters("@Param2") = myParamTwo

     .Execute

  End With

  Works like a champ.  You can also do Set rs = .Execute to get a recordset of results returned by the Command.