How to Select from Stored Procedure

How to SELECT FROM stored procedure

You can use a User-defined function or a view instead of a procedure.

A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.

How to select from stored procedure output

You could store the result of the stored procedure into a temporary table (or a table variable, or even a physical table) and then query that one:

--drop table if exists #temp
create table #temp(name nvarchar(100), rows int, reserved nvarchar(100), data nvarchar(100), index_size nvarchar(100), unused nvarchar(100))

insert into #temp
exec sp_spaceused 'dummytable'

select [rows], [reserved] 
from #temp

How to call Stored Procedure in Views?

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

Stored procedure with select as parameter

You cannot have expressions or selects in the middle of the EXEC - you need to first select the value you want to use into a variable, and then call your stored procedure - like this:

DECLARE @Input VARCHAR(20);

SELECT @Input = 'AD1801' +
                CONVERT(VARCHAR(2), GETDATE(), 101) + 
                CONVERT(VARCHAR(2), GETDATE(), 103)

EXEC dbo.SP_CARGA_NUEVOS @Input

In MYSQL, can a Stored Procedure be set up to be used without permissions to underlying SELECT tables?

Grant SELECT permission on the tables only to a special username. Then make that user the definer of the procedure and use the SQL SECURITY DEFINER option in the CREATE PROCEDURE statement.

Give the ordinary users EXECUTE permission on the procedures (see https://dba.stackexchange.com/questions/97719/how-to-grant-execute-on-mysql).

With these procedure settings, when the procedure is running it has permissions based on the definer, allowing it to access tables that the user can't access directly.

How to return an id and use it directly in another stored procedure?

If you want to return something from stored procedure to the context of SQL query execution you may use a return statement or an output parameter. I would suggest you to use the second option. The first one is generally intended to return status of procedure execution.

ALTER PROCEDURE [dbo].[InsertAddress_DBO]
    @Name VARCHAR(50),
    @Address_ID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[Address]([Address_Name])
    VALUES (@Name)
    SET @Address_ID = SCOPE_IDENTITY()
END

Than you can use returned value in your outer procedure

ALTER PROCEDURE [dbo].[InsertEstablishmentByStrings_DBO]
    @Establishment_Name VARCHAR(50),
    @Address_Name VARCHAR(50),
    @Documentation_Text VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Address_ID INT ,
        @Documentation_ID INT 

    EXEC [dbo].[InsertAddress_DBO] 
        @Address_ID = @Address_ID OUTPUT,
        @Name = "rue de la banchiesserie 85 Golback"

     ...
END

An OUTPUT INSERTED clause you used doesn't returns data to the query execution context but send them to the output stream.

---

Related Topics