SQL 2000 Table Variables & UDFunctions

• jk78

  SSC Journeyman

  Points: 94

  More actions

  November 17, 2006 at 3:41 am

  #368070

  Hi Guys,

  Novice Access SQL programmer here.

  SQL 2000 Enterprise DB with Access 2000 mdb front end.

  I have been looking at improving speed for some of my subforms. One form in particular is based on an Access Query with loads & loads of expressions (including some VBA functions) which is in turn based on a View.

  So far I have converted the Access query with all its expressions to a stored procedure. This has a hugely positive impact on performance.

  Thing is I would like to retain the flexibility of the View and Ive been experimenting with a UDF returning a table to the calling View. This seems to work great. Too Great!

  I need to be able to update/insert/delete records from the View which all seems possible but Im a little apprehensive about rolling this out to production.

  Should I be using this technique for updating base tables? Are there any side effects of this method I should be wary of?

  BASE TABLE > UDF > TABLE VARIABLE > VIEW

  I havent used UDFs or Table variables at all before

  Any advice will be gratefully accepted.

  Thanks.

  JK

• Lowell

  SSC Guru

  Points: 323522

  More actions

  November 17, 2006 at 10:27 am

  #672689

  you will have to update the base tables;

  a view is really just a saved SELECT statement. this article might help a little bit:

  http://www.sql-server-performance.com/nn_views.asp

   

  you'd most likely want to create a stored procedure , which would be passed some of the values you want to change(parameters), and that would in turn update the base tables.

  Of course, you can just use a SQL Statement to update the table directly, but with a stored proc, you get an additional performance boost because of saved code and execution plans.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!