Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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]



Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap