Temporary Tables,Table Variables,and Table-Valued Functions in SQL Server

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Jan-2002 >

Temporary Tables,Table Variables,and Table-Valued Functions in SQL Server

 

Burton Roberts

In this article, Burton Roberts shows how to use SQL
Server’s temporary tables in your Access Data Projects. With
that out of the way, he’ll introduce two powerful new
features of SQL Server 2000.

 

WHEN you construct a complex query in Access/

Jet, it’s sometimes useful to break the query up

into simpler queries and combine them. Access

and Jet allow you to both create queries that join and

embed parameterized queries with other parameterized

queries. When you run a query from the database

window, you’re prompted for the parameters of the

embedded queries as well as the outer query. If you have

compound queries like this in your Access/Jet database

and you intend to upsize your database to use SQL Server

7, you’re going to be disappointed: These queries don’t

translate to the new environment.

 

In SQL Server, Jet queries are replaced by views and

stored procedures. You can create a view using the

graphical designer, which looks similar to the query

designer in Access/Jet. You can join views together and

embed them within other views. Views, however, don’t

take parameters. In order to add parameters to your view,

you must embed the view in a stored procedure. A simple

example would look like this procedure, which embeds a

SQL query in a stored procedure:

 

CREATE PROCEDURE procMyProcedure

@intParam int

AS

SET NOCOUNT ON

SELECT column1, column2

FROM vwMyView

WHERE column3 = @intParam

RETURN

 

When you run procMyProcedure from the Access

Project container, you’ll be prompted for the integer

parameter, @intParam, and then the query will run.

You can also join views in your stored procedure

and add parameters like this:

 

Read More Here:

Temporary Tables, Table Variables, and Table-Valued Functions in SQL