How to call Stored Procedure in Views?

Question

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)

**DO NOT EVER** use [`sp_`](https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix) prefix, it's reserved for System Stored Procodures by MS. — Ilyes, Jan 23 '19 at 09:52
To add to the comment from @Sam : [Is the sp_ prefix still a no-no?](https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix). How do you expect us to convert your (unparametrised) Stored Procedure into a View without the SP's DDL? We can't see your SQL, nor do we have a crystal ball. Please help us help you. — Thom A, Jan 23 '19 at 09:57
You can't "`SELECT`" from a Stored procedure, no; you execute it. If you need to have replicate the result set from an Stored Procedure into a view, then you need to use the `SELECT` statement inside your SP. — Thom A, Jan 23 '19 at 10:12
I did Select statement at the end of my sp. That is why i'm getting that result table. How do I use this result table as a reference table (example temporary table, or virtual table (Views)? — Safwan Masarik, Jan 23 '19 at 10:17
It's not possible to call an stored procedure within a view. An alternative could be to convert your stored procedure into a table-valued function. — Marc Guillot, Jan 23 '19 at 10:31
Possible duplicate of [SQL Server - SELECT FROM stored procedure](https://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure) — Ilyes, Jan 23 '19 at 10:32

score 2 · Answer 1 · edited Jan 23 '19 at 10:20

2

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.

edited Jan 23 '19 at 10:20

Adrien Brunelat

4,492
4
29
42

answered Jan 23 '19 at 10:02

Daniel Attard

102
1
8

Since his `SP` returns a table, sure he `SELECT FROM TABLE(s)`. – Ilyes Jan 23 '19 at 10:10
Yes I did Select in the SP. How do you execute in Views. And how do I execute this SP in Function given that I don't pass any parameter? – Safwan Masarik Jan 23 '19 at 10:12
You can't execute stored procedure within views or functions. Stored Procedure are not intended for that use. You'll have to write a proper view or a proper function, with all the code to retrieve the data you need. – Marc Guillot Jan 23 '19 at 10:39
Usually what I do in SQL server management studio is: New Query (CTRL +N), then type : exec STORED_PROCEDURE_NAME. If you just need a view you have to create a view which is totally different from a Function or a StoredProcedure – Daniel Attard Jan 23 '19 at 10:42
If you need an example of a view here is a sample that queries 2 tables using join http://www.sqlfiddle.com/#!9/fd2a3b/1 – Daniel Attard Jan 23 '19 at 11:13

Marc Guillot · Accepted Answer · 2019-01-23T11:55:32.327

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

Hi Marc, thanks for you answer. Please refer my updated question. I tried inserting my procedure 'declare @empTable table .. ' . However it's red on 'declared' . Can we declare variable inside the RETURN ()? — Safwan Masarik, Jan 23 '19 at 11:11
Hi Safwan, I have provided two syntaxes for table-valued functions. If you want to declare variables and use multiple instructions, you have to use the second syntax : multi-statement table-valued functions. That syntax doesn't have a RETURN section. — Marc Guillot, Jan 23 '19 at 11:31
Hi Mark, yes I implemented the same thing and works charm. You're a genius!! — Safwan Masarik, Jan 23 '19 at 15:19

Stanislav Kundii · Answer 3 · 2019-01-23T10:08:37.740

-1

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

edited Jan 23 '19 at 10:08

answered Jan 23 '19 at 09:58

Stanislav Kundii

2,874
1
11
17

I love this answer, almost similar to my research. However can you explain the Add linked server (local) part and Openquery part. I don't understand how it works? – Safwan Masarik Jan 23 '19 at 10:24

How to call Stored Procedure in Views?

3 Answers3