使用存储过程创建视图 [英] Creating a View using stored procedure

问题描述

这个问题之前已经问过几次了，很遗憾我的问题没有得到答案.

This questions have asked few times before, unfortunately I did not get an answer to my questions.

好吧，我有两个 SQL (SQL SERVER 2008) 表，Employee 和 Employee expens，其中 Employee Id 分别是主键和外键.

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.

员工表列，1. 员工 ID(P 键) 2. 经理 3. 位置 4. 加入日期 5. 姓名

Employee table columns, 1. Employee Id (P Key) 2. Manager 3. Location 4. Join Date 5. Name

员工费用表列，1. 费用 ID(P 键) 2. 员工 ID(F 键) 3. 费用类型 4. 费用金额 5. 费用日期.

Employee Expense table columns, 1. Expense Id (P Key) 2. Employee Id (F key) 3. Expense Type 4. Expense Amount 5. Expense Date.

问题是，我想创建一个要在 SharePoint Web 部件中使用的视图，我将在其中查询两个表，所以我的要求是使用以下列创建一个视图，

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,

从员工那里我需要员工 ID 和姓名.从员工费用中，我需要费用类型、费用金额、费用日期.

From Employee I need Employee Id and Name. From Employee Expenses I need Expense Type, Expense Amount, Expense Date.

其他要求.

一个.如果我在 Employee Expense 表中有一个员工的多个条目，那么视图中应该有很多行

b.即使我在 Employee Expense 表中没有条目，我也应该在视图中获取该特定 Employee 的行，Employee Expense 表列为 null.

请帮助我继续...

编辑 按照 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])

请帮忙.

推荐答案

如果要从 SP 中创建视图，则需要使用动态 SQL.

If you want to create a view from within a SP you need to use dynamic SQL.

类似的东西.

create procedure ProcToCreateView 
as
exec ('create view MyView as select 1 as Col')

create view... 代码必须作为字符串参数发送给 exec，从它的外观来看，您已经拥有视图所需的代码所以只需将它嵌入 ' 之间.

The create view... code has to be sent as a string parameter to exec and by the looks of it you already have the code you need for the view so just embed it in between the '.

我真的不知道你为什么需要那个.也许您只需要知道如何使用来自 SP 的视图

I really have no idea why you need that. Perhaps you just need to know how to use a view from a SP

create procedure ProcToUseView
as
select Col
from MyView

这篇关于使用存储过程创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！