Can I create variables inside a stored query? Do stored queries have language constructs like if-then, variable assignment and looping constructs?

I would like to create all of my queries as stored queries as I anticipate upscaling to SQL Server in the future and want to migrate the queries from Access to SQL as opposed to program code to SQL.

Access querydefs are not the same as stored procedures. They do not support procedural language constructs nor variable assignments. They do support SQL, Access and user-defined functions, and parameters.

If you name your tables and columns so that they conform to SQL Server standards, you will be able to upsize to SQL Server with minimal problems.

Using stored querydefs rather than dynamic SQL created in VBA is more efficient since the querydefs are parsed and optimized when they are stored and each time the database is compacted. Dynamic SQL needs to be parsed and a query plan needs to be created EVERY time they are run.

Thanks for the reply.

Could I theoretically, accomplish all the procedure language tasks in a user-defined function and call that function from the stored querydef?

What I find myself doing is writing several querydefs to accomplish a task that I would normally be able to do inside a stored procedure. Like if I want to query table A and get a column value before I insert a new row in table B. Simple tasks but I end up writing two very simple querydefs and calling each one individually as opposed to just calling one querydef.

How would you typically handle this type of situation?

Chris

You can certainly define some very complex linguistic actions in a function and call your function from a query.

You must do the following:

1. Put the function in a general module.

2. Declare it as a Public Function that returns the data type you need.

3. This is tricky. Assure the function is self-contained, depending ONLY on the input parameters. I.e. you DO NOT want to use global side-effects. But on the other hand, it is legal to call a function or procedure from a function, so that aspect of it is NOT what I meant by self-contained. I guess what I mean is that you cannot assume anything about your execution surroundings that you don't either directly program or pass into the function as a parameter.

For instance, if you have declarations in the general section of your general module, you cannot assume that they are initialized when you first run the function. A query does not necessarily instantiate a function call from a general module in exactly the same way that a form or report might do so for a class module. It might actually do so but you can't COUNT on it to do so.

4. Make the function as non-stop as possible. That includes what to do about error handling and how to treat bad inputs and all sorts of stuff like that. (Unless, of course, you wish to allow bad input to abort your query, but that is probably a bad idea.)

Access doesn't directly support views. What it does do is to allow you to nest queries. Therefore query1 can use a join of table1 and query2 to produce an updateable recordset.

Although you can use Access and user-defined functions in your queries, you need to be a little careful with how they are used if you want to avoid performance problems when you upsiae. Jet will parse your queries and attempt to construct a query that it can pass to the ODBC driver for your back-end database. You want to make sure that you don't create queries that can't be evaluated by the backend. For example look at the two samples below. The first will prompt for the parameters and then be passed to SQL and only the selected rows will be returned but the second will need to be evaluated locally to process the user-defined function so Jet will request the ENTIRE table from SQL.

Select * From YourTable Where YourDate Between [Enter Start Dt] and [Enter End Dt];

Select * From YourTable Where YourDate Between Func1(YourDate) And Func2(YourDate);