Creating a View using stored procedure

Question

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.

Employee table columns, 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.

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,

From Employee I need Employee Id and Name. From Employee Expenses I need 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

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.

Please help me to proceed ...

Editing To add the required view code as the Stack Overflow members instructed !!

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.

Answer 1

If you want to create a view from within a SP you need to use dynamic 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 exec and by the looks of it you already have the code you need for the view so just embed it in between the '.

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