开发者

How to return a view data using stored procedure

Hi friends the requirement is that using a stored procedure I should return all the value from a view.

Currently we are having a storedprocedure to return all the values from a table as given,

CREATE PROCEDURE [dbo].[SuperGas_GetAllEmployees] 
    AS
BEGIN

SELECT [Employee ID]
      ,[First Name]
      ,[Last Name]
      ,[Group]
      ,[Location]
      ,[DOB]
      ,[DOJ]
      ,[Manager]
      ,[Projects]
      ,[Phone]
      ,[Extension]
  FROM [SuperGas].[dbo].[Employee]
END

We have created a view by joining two tables named ExpenseView as given,

CREATE VIEW [dbo].[ExpenseView] AS
SELECT Emp.[Employee ID], Emp.[First Name],Emp.[Last Name],Emp.Manager, Ep.[Expense Type],Ep.[Expense Amount],Ep.[Expense Date]
FROM [Employee Expense] as Ep
FULL JOIN Employee as Emp
ON EP.[Employee ID] = Emp.[Employee ID]

Now the question here is like in the first stored procedure, how can I return all the columns of the above view (Expense) using a stored procedure. We tried using the same way as the first procedure; however the View ExpenseView is not identified in the query.

History,

From my previous question I came to know, it is not good to use view with a Storedprocedure.

However our application is using the below c# code to get data from the Database using a stored Procedure,

string spName = "SuperGas_GetAllEmployees";

 DataSet ds = GetDatasetFromAdapter(spName);

Changing this needs extensive time and extra resources for the coding. Hence is there any way I can return the entire View (Like what we did in the given procedure SuperGas_GetAllEmployees for Tables) So that I can make use of current Code itself.

Thank you for all your Help

开发者_JS百科Editing ....

CREATE PROCEDURE [dbo].[SuperGas_GetAllExpenseView] 
    AS
BEGIN

    SELECT [Employee ID]
      ,[First Name]
      ,[Last Name]
      ,[Manager]
      ,[Expense Type]
      ,[Expense Amount]
      ,[Expense Date]
  FROM [SuperGas].[dbo].[ExpenseView]
END

GO

Here I am getting error for the fields Expense Type, Expense Amount, Expense Date and for the view name ExpenseView

Please help me


CREATE VIEW [dbo].[ExpenseView] AS

Actually where the view is created? In your stored procedure you use FROM [SuperGas].[dbo].[ExpenseView] Could you please ensure that the view is inside of [SuperGas]

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜