RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Easily Access Remote Data Using SQL Server Views : Page 3

Getting to the data you need is always a challenge. Views can provide a "no muss, no fuss" way to retrieve the data you need—whether it's in the current database or another.

Add Flexibility with a Stored Procedure
Some developers complain that views don't accept parameters, but that's no reason to avoid them. When a view alone isn't flexible enough, use a stored procedure to call it. Combined, a view and a stored procedure create a versatile, flexible, and easy way to retrieve data from another SQL Server database.

You can easily call a view from a stored procedure as follows:

@variable datatype
SELECT * | fieldlist 
FROM database.owner.viewname 
WHERE field = @variable 

For example, you could create the following stored procedure to limit further the records retrieved by vUpdateProductionLocation:

CREATE PROCEDURE spRunViewWithPara @costrate smallmoney
SELECT * FROM AdventureWorks.dbo.vUpdateProductionLocation
WHERE CostRate > @costrate

Being able to change the parameter provides a more dynamic solution than a view by itself. Figure 2 shows the result of executing this stored procedure from inside the Sales database.

Figure 2. Calling a View from a Stored Procedure: Combined with a stored procedure, a view becomes a versatile tool for retrieving data from another database.

The view can limit data via the SELECT field list and a WHERE clause.

Noteworthy Tips for Quick, Easy Views
As you have seen, views can make retrieving data from another SQL Server database quite easy. To enhance your view solution even further, keep the following tips in mind:

  • For more flexible filtering, pass a parameter via a stored procedure (see previous section).
  • If you are worried about the performance of views, consider an indexed view.
  • Views can work with linked databases, but the syntax is different:
    CREATE VIEW [databasename].[owner].viewname[
     (column [n, ...])]
    [WITH attribute [n, ...]]
    SELECT *|fieldlist FROM linkeddb...table AS alias

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date