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.

What have you tried? If you want to do code-by-request then you should hire a consultant. Stack Overflow is for assistance with specific issues. — JNK, Oct 10 '11 at 12:32
Sorry guys, I dint know that for newbies there is no help in Stack overflow. Thank you ... — Jithu, Oct 10 '11 at 12:35
@Jithu - what JNK is explaining to you, is that you need to show us that you've put some effort into trying to solve this problem yourself. Otherwise, it just looks like you came here to have someone do your work for you. — Josh Darnell, Oct 10 '11 at 12:36
@Jithu - **Newbies are welcome!** Help vampires are not. Just show that you tried to solve it on your own. When you post a "question" which is really just a list of requirements, expecting someone to solve it for you, you are less likely to get good help. — JNK, Oct 10 '11 at 12:42
Okay fine, let me come back with my code, Since I am new here I was not knowing the exact procedures, for that they should have put one comment instead of putting a negative vote :) — Jithu, Oct 10 '11 at 12:42
'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])' I want to create this view using a stored procedure, This one working perfect. I am not sure how to do the same with Procedures — Jithu, Oct 10 '11 at 13:12
Everything you need to know about how to start on SO cen be read up here http://stackoverflow.com/faq — , Oct 10 '11 at 14:04
You should not be using implicit syntax, that is a SQl antipattern and a very poor programming techinique. Don't get into bad habits of using syntax that was replaced with something better almost 20 years ago. — HLGEM, Oct 10 '11 at 14:54

score 17 · Accepted Answer · answered Oct 10 '11 at 13:57

17

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

answered Oct 10 '11 at 13:57

Mikael Eriksson

136,425
22
210
281

1

Of course in real life, you shoudl not want to create a view from a stored proc. It is a isign that you are doing something wrong if you want to do that. Views are created once and do not need to be created repeatedly. – HLGEM Oct 10 '11 at 14:56
HLGM its a part of performance testing in our product, In actual case we really did not use it. Thank you for your help... – Jithu Oct 10 '11 at 17:17
4

@HLGEM I disagree. We have a process that automatically creates a specific database, some standard tables within it and a view. Creating a view within an SP is useful for this. – al_sweets Jul 16 '19 at 14:35

Felix Augustin · Answer 2 · 2012-02-25T05:57:16.317

5

I use the following dynamic SQL code in my SQL database to create a view with a store procedure. It works fine:

    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
    ')

edited Feb 25 '12 at 05:57

answered Feb 25 '12 at 05:47

Felix Augustin

59
2
2

Jamie F · Answer 3 · 2011-10-10T13:34:08.247

1

Using Joins from MS BOL

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.

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

edited Oct 10 '11 at 13:34

answered Oct 10 '11 at 13:22

Jamie F

23,189
5
61
77

Hi Jamie, Thanks for your time. Here question is, how can I put this whole creating the view function into a stored procedure !! – Jithu Oct 10 '11 at 13:31
Now you are changing your question entirely. Have you googled or searched on this? When I google "Create View from SP SQL" the second hit listed is a detailed description of this. – Jamie F Oct 10 '11 at 13:39
Hi Jamie your question indeed help me in my two additional questions I have mentioned. However the real question was to create a view from stored procedure. Thank you for your help. – Jithu Oct 10 '11 at 17:20

Creating a View using stored procedure

3 Answers3

Linked

Related