CODEWITHSUNDEEP
sqlsql-serverstored-proceduresviewsql-function
Safwan Masarik
Safwan Masarik

Reputation: 131

How to call Stored Procedure in Views?

My stored procedure do not have parameters. Running it as below:-

EXEC sp_Email_spd_CasualNotification

Returns a table:-

DeptChangeID| oldDeptID |DeptID | JobID |EmpID
33419       | NULL      | 679   | 3742  |16575
42392       | NULL      | 783   | 4742  |100000378

I want to view this table in View. I just can't find a way. What I meant was 

CREATE View [dbo].[vw_EmploymentLatest] as  
Select * from (EXEC sp_Email_spd_CasualNotification)

This is not possible.

My stored procedure simplified as below:-

declare @empTable table (employeeID int)
declare @selectedDeptChangeIDTable table (deptChangeID int)
declare @rowCount int
declare @rowNum int
declare @selectedDeptChangeID int
declare @empID int

Insert into @empTable (employeeID)
SELECT DISTINCT
E.Employee_ID
FROM Employee E
'
'

WHILE exists (select * from @empTable)
BEGIN    
SELECT @empID = (select top 1 employeeID from @empTable order by employeeID asc)

'
'

WHILE @rowNum <= @rowCount
BEGIN
declare @p1 int
declare @p2 int
'
'
'
'
DELETE @empTable WHERE employeeID = @empID 
END

Select * from Employment Where DeptChangeID in (Select deptChangeID from @selectedDeptChangeIDTable)

Upvotes: 0

Views: 3298

Answers (3)

Marc Guillot
Marc Guillot

Reputation: 6455

Stored procedures are not intended to be used within views (or functions). You'll have to write your query on the view or alternatively on a function.

Lets suppose this stored procedure :

CREATE PROCEDURE dbo.Test
AS
    SELECT * FROM sys.tables
GO

EXEC dbo.Test

It can be converted into a table-valued function like this :

CREATE FUNCTION dbo.Test
RETURNS TABLE AS
RETURN (
  SELECT * FROM sys.tables
)
GO

SELECT * FROM dbo.Test()

Now this can be used within a view, if you want to.

That was the simpler syntax (in-line function) but for any complex stored procedure you will need to convert it into a multi-statement table-valued function.

CREATE FUNCTION dbo.Test
RETURNS @Test TABLE (object_id int, name varchar(100)) 
AS
BEGIN
  INSERT INTO @Test (object_id, name)
         SELECT object_id, name FROM sys.tables
END
GO

SELECT * FROM dbo.Test()

Looking at the code you have provided, your function would be something like this :

CREATE FUNCTION dbo.Email_spd_CasualNotification
RETURNS @CasualNotification TABLE (DeptChangeID int, oldDeptID int, DeptID int, JobID int, EmpID int) 
AS
BEGIN
  declare @empTable table (employeeID int)
  declare @selectedDeptChangeIDTable table (deptChangeID int)
  declare @rowCount int
  declare @rowNum int
  declare @selectedDeptChangeID int
  declare @empID int

  Insert into @empTable (employeeID)
         SELECT DISTINCT
         E.Employee_ID
         FROM Employee E
         '
         '

  WHILE exists (select * from @empTable)
  BEGIN    
    SELECT @empID = (select top 1 employeeID from @empTable order by employeeID asc)

    '
    '

    WHILE @rowNum <= @rowCount
    BEGIN
      declare @p1 int
      declare @p2 int
      '
      '
      '
      '
      DELETE @empTable WHERE employeeID = @empID 
    END
  END  

  insert into @CasualNotification(DeptChangeID, oldDeptID, DeptID, JobID, EmpID)
         Select DeptChangeID, oldDeptID, DeptID, JobID, EmpID 
         from Employment 
         Where DeptChangeID in (Select deptChangeID from @selectedDeptChangeIDTable)
END

Upvotes: 1

Danny
Danny

Reputation: 100

Your procedure should contain a SELECT statement on the table(s) you want to query.

Example:

SELECT * FROM myTable

Then you call your procedure:

EXEC sp_Email_spd_CasualNotification

If your procedure is not doing any alterations to the tables why not consider using a FUNCTION?

Note: The sp_ prefix is reserved for System Stored Procedures, so I would change the name.

Upvotes: 2

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

CREATE PROCEDURE dbo.Test
AS
    SELECT * FROM sys.tables
GO

EXEC dbo.Test

upd: for use in view (why? may by table/inline function?)

add linked server (local)

EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

and try

CREATE VIEW dbo.vw
AS
SELECT * 
FROM       OPENQUERY(LinkedServer, 'EXEC [YOURDB].dbo.Test') AS a
GO

SELECT * FROM dbo.vw

Upvotes: -1

Related Questions