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: