Easily Access Remote Data Using SQL Server Views : Page 3

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:

CREATE PROCEDURE
 [databasename].[owner].storedprocedurename
@variable datatype
AS
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
AS 
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, ...]]
  AS
  SELECT *|fieldlist FROM linkeddb...table AS alias
  [WITH CHECK OPTION]
  

Previous Page