SQL 使用存储过程创建视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7712702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a View using stored procedure
提问by Jithu
This questions have asked few times before, unfortunately I did not get an answer to my questions.
这个问题以前问过几次，不幸的是我没有得到我的问题的答案。
Well I have two SQL (SQL SERVER 2008) tables, Employee and Employee expens, where Employee Id is the Primary key and the foreign key respectively.
好吧，我有两个 SQL ( SQL SERVER 2008) 表，Employee 和 Employee expens，其中 Employee Id 分别是主键和外键。
Employee table columns, 1. Employee Id (P Key) 2. Manager 3. Location 4. Join Date 5. Name
Employee 表列，1. Employee Id (P Key) 2. Manager 3. Location 4. Join Date 5. Name
Employee Expense table columns, 1. Expense Id (P Key) 2. Employee Id (F key) 3. Expense Type 4. Expense Amount 5. Expense Date.
员工费用表列，1. 费用 ID（P 键） 2. 员工 ID（F 键） 3. 费用类型 4. 费用金额 5. 费用日期。
Question is, I want to create a view to be used in a SharePoint web part, where I will query both table, So my requirement is to create a view using following Columns,
问题是，我想创建一个要在 SharePoint Web 部件中使用的视图，我将在其中查询两个表，所以我的要求是使用以下列创建一个视图，
From Employee I need Employee Id and Name. From Employee Expenses I need Expense Type, Expense Amount, Expense Date.
从 Employee 我需要Employee Id 和 Name。从 Employee Expenses 我需要Expense Type, Expense Amount, Expense Date。
Additional requirements.
其他要求。
a. If I have multiple entries for an employee in the table Employee Expense, that many no of rows should be there in the View
一种。如果我在 Employee Expense 表中有一个员工的多个条目，那么视图中应该有很多行
b. Even If I have no entry in the Employee Expense table, then also I should get the row for that particular Employee in the view, with null for the Employee Expense table columns.
湾 即使我在 Employee Expense 表中没有条目，我也应该在视图中获取该特定 Employee 的行，而 Employee Expense 表列则为 null。
Please help me to proceed ...
请帮助我继续...
Editing To add the required view code as the Stack Overflow members instructed !!
编辑 按照 Stack Overflow 成员的指示添加所需的视图代码！！
CREATE VIEW ExpenseView AS (
SELECT [Employee Expense].[Employee ID], Employee.[First Name], [Employee Expense].[Expense Type],[Employee Expense].[Expense Amount],[Employee Expense].[Expense Date]
FROM Employee,[Employee Expense]
WHERE [Employee Expense].[Employee ID] = Employee.[Employee ID])
Please help.
请帮忙。
回答by Mikael Eriksson
If you want to create a view from within a SP you need to use dynamic SQL.
如果要从 SP 中创建视图，则需要使用动态 SQL。
Something like this.
像这样的东西。
create procedure ProcToCreateView
as
exec ('create view MyView as select 1 as Col')
The create view...code has to be sent as a string parameter to execand by the looks of it you already have the code you need for the view so just embed it in between the '.
该create view...代码必须发送一个字符串参数exec，并通过你已经拥有你需要的视图代码它的外观所以只是将其嵌入在之间'。
I really have no idea why you need that. Perhaps you just need to know how to usea view from a SP
我真的不知道你为什么需要那个。也许您只需要知道如何使用来自 SP 的视图
create procedure ProcToUseView
as
select Col
from MyView
回答by Felix Augustin
I use the following dynamic SQL code in my SQL database to create a view with a store procedure. It works fine:
我在我的 SQL 数据库中使用以下动态 SQL 代码来创建一个带有存储过程的视图。它工作正常：
CREATE PROCEDURE uspCreateView
AS
EXEC ('
CREATE VIEW vwDataLayoutFileAssignment
AS
SELECT b.FileID, d.FieldID
FROM [File] b, DataLayoutFileAssignment c, [Field] d
WHERE b.DriverFileID = c.FileID
AND C.DataLayoutID = d.DataLayoutID
')
回答by Jamie F
And from a close page
从关闭页面
Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.
但是，外连接条件与 WHERE 子句搜索条件的交互可能不同，具体取决于连接条件是在 FROM 还是 WHERE 子句中。因此，不推荐在 WHERE 子句中指定 Transact-SQL 外连接的功能，不再记录，并将在未来版本中删除。
So that turns your code into:
这样你的代码就会变成：
CREATE VIEW ExpenseView AS
BEGIN
SELECT [Employee Expense].[Employee ID], Employee.[First Name], [Employee Expense].[Expense Type],[Employee Expense].[Expense Amount],[Employee Expense].[Expense Date]
FROM Employee
LEFT OUTER JOIN [Employee Expense]
ON [Employee Expense].[Employee ID] = Employee.[Employee ID]
END
