Home » SQL ServerRSS

Can a view call a stored procedure

I was wondering if a SP can be called from a view?  I have a SP that has a couple of temporary tables that I'm using to build some data.  The end result is a select statement that returns the data I have built up.  I would like for the user to be able to access a view rather than have to run the SP.

 

Nick

 

5 Answers Found

 

Answer 1

Try writing he stored  procedure as table function -- especially an inline table function.  The short answer is that a view  cannot directly call  a stored procedure  but it can call a function. 

There is a potential workaround that you might be able to use that uses "openRowset" might be able to convert your stored procedure output through what looks more like a function reference.  However, this is considerably bad way of getting the job done.

Another alternative is to use the syntax INSERT INTO yourTable EXEC yourStoredProcedure to load the data  into a (temp) table and then use the temp table in select  querie(s).  But this doesn't provide a ready-made method of dropping something like this into a view.

My first suggestion remains: try writing the stored procedure as a table function.

 

Answer 2

Sorry, but SQL does not allow you to do so,
 

Answer 3

Just to add what Kent W. mentioned,

You **cannot** call  stored procedure  from a view. view  can only make use of a SELECT statement. You will have to use an inline table valued function (which acts more like parameterized view)

>>I have a SP that has a couple  of temporary  tables that I'm using to build  some data

If somereason you can not use inline function, you can attempt to use multi statement  function, (acts more like a table variable) but generally they do not produce good execution plan.

 

Answer 4

I don't have much experience with functions but this what I'm trying to accomplish and it isn't working.

 

CREATE FUNCTION dbo.emaillist


BEGIN
create table #All_active_employees(
        employee_id_nb varchar(30))
insert into #All_active_employees (employee_id_nb)
        select distinct employee_id_nb
        from dwview.dbo.employee_position_c as ep
        inner join dwview.dbo.position_c as p
            ON p.position_id = ep.position_id
        where start_dt < getdate()
            and (end_dt > getdate() OR end_dt is null);


CREATE TABLE #Emp_With_Pos_Not_PTF(
        employee_id_nb  varchar(30) )
INSERT INTO #Emp_With_Pos_Not_PTF
        select  distinct employee_id_nb
        from dwview.dbo.employee_position_c as ep
        inner join dwview.dbo.position_c as p
            ON p.position_id = ep.position_id
            and p.Position_Type_Cd not in ('FP','SR','SP')
        where start_dt < getdate()
          and (end_dt > getdate() OR end_dt is null);

CREATE TABLE #Emp_Of_Interest(
        employee_id_nb  varchar(30) )

INSERT INTO #Emp_Of_Interest
        Select aae.employee_id_nb
        from #All_active_employees as aae
        left outer join #Emp_With_Pos_Not_PTF as ewp
            ON ewp.employee_id_nb = aae.employee_id_nb
        where ewp.employee_id_nb is null;

/* make an employee_of_interest that can have more than one active position,
          but all positions are part time faculty */


CREATE TABLE #EMP_Teaching_Now (
        employee_id_nb varchar(30)
        )
INSERT INTO #EMP_Teaching_Now
SELECT distinct eoi.employee_id_nb
        from #Emp_Of_Interest as eoi
        inner join DWVIEW.dbo.course_Section_faculty_rel_c as csfr
            ON csfr.faculty_id_nb = eoi.employee_id_nb
        inner join dwview.DBO.course_section_c as cs
            ON cs.course_section_id = csfr.course_section_id
            and GETDATE() between cs.start_dt and cs.end_dt
            and cs.status_cd = 'ACTIV';

CREATE TABLE #EMP_Teaching_Later (
        employee_id_nb varchar(30)
        )
INSERT INTO #EMP_Teaching_Later
SELECT distinct eoi.employee_id_nb
        from #Emp_Of_Interest as eoi
        inner join DWVIEW.dbo.course_Section_faculty_rel_c as csfr
            ON csfr.faculty_id_nb = eoi.employee_id_nb
        inner join DWVIEW.dbo.course_section_c as cs
            ON cs.course_section_id = csfr.course_section_id
            and GETDATE() < cs.start_dt
            and cs.status_cd = 'ACTIV';


CREATE TABLE #Emp_Teaching_Last (
        employee_id_nb varchar(30)
        )
INSERT INTO #Emp_Teaching_Last
SELECT distinct eoi.employee_id_nb
        from #Emp_Of_Interest as eoi
        inner join DWVIEW.dbo.course_Section_faculty_rel_c as csfr
            ON csfr.faculty_id_nb = eoi.employee_id_nb
        inner join dwview.dbo.course_section_c as cs
            ON cs.course_section_id = csfr.course_section_id
            and GETDATE() - 90 between cs.start_dt and cs.end_dt
            and cs.status_cd = 'ACTIV';


CREATE TABLE #Emp_Teaching_Last_Not_Now (
        employee_id_nb varchar(30)
        )
INSERT INTO #Emp_Teaching_Last_Not_Now
SELECT etlast.employee_id_nb
        from #Emp_Teaching_Last as etlast
        left outer join #EMP_Teaching_Later etl
            ON etlast.employee_id_nb = etl.employee_id_nb
        where etl.employee_id_nb is null
        AND etlast.employee_id_nb NOT IN (Select employee_id_nb FROM #EMP_Teaching_Now);
END

select * from #Emp_Teaching_Last_Not_Now
    


 

Answer 5

It's best to tell us what isn't working (which error you are getting, or you get the wrong results, or no results or whatever).  But try

CREATEFUNCTION dbo.emaillist() 
Returns @Emp_Teaching_Last_Not_Now Table (
    employee_id_nb varchar(30)
    )
BEGINDeclare @All_active_employees table (
    employee_id_nb varchar(30))
insertinto @All_active_employees (employee_id_nb)
    selectdistinct employee_id_nb
    from dwview.dbo.employee_position_c as ep
    innerjoin dwview.dbo.position_c as p
      ON p.position_id = ep.position_id
    where start_dt < getdate()
      and (end_dt > getdate() OR end_dt isnull);


DECLARE @Emp_With_Pos_Not_PTF TABLE(
    employee_id_nb varchar(30) )
INSERTINTO @Emp_With_Pos_Not_PTF
    selectdistinct employee_id_nb
    from dwview.dbo.employee_position_c as ep
    innerjoin dwview.dbo.position_c as p
      ON p.position_id = ep.position_id
      and p.Position_Type_Cd notin ('FP','SR','SP')
    where start_dt < getdate()
     and (end_dt > getdate() OR end_dt isnull);

DECLARE @Emp_Of_Interest TABLE(
    employee_id_nb varchar(30) )

INSERTINTO @Emp_Of_Interest
    Select aae.employee_id_nb
    from @All_active_employees as aae
    leftouterjoin @Emp_With_Pos_Not_PTF as ewp
      ON ewp.employee_id_nb = aae.employee_id_nb
    where ewp.employee_id_nb isnull;

/* make an employee_of_interest that can have more than one active position,
     but all positions are part time faculty */DECLARE @EMP_Teaching_Now TABLE(
    employee_id_nb varchar(30)
    )
INSERTINTO @EMP_Teaching_Now
SELECTdistinct eoi.employee_id_nb
    from @Emp_Of_Interest as eoi
    innerjoin DWVIEW.dbo.course_Section_faculty_rel_c as csfr
      ON csfr.faculty_id_nb = eoi.employee_id_nb
    innerjoin dwview.DBO.course_section_c as cs
      ON cs.course_section_id = csfr.course_section_id
      andGETDATE() between cs.start_dt and cs.end_dt
      and cs.status_cd = 'ACTIV';

DECLARE @EMP_Teaching_Later TABLE(
    employee_id_nb varchar(30)
    )
INSERTINTO @EMP_Teaching_Later
SELECTdistinct eoi.employee_id_nb
    from @Emp_Of_Interest as eoi
    innerjoin DWVIEW.dbo.course_Section_faculty_rel_c as csfr
      ON csfr.faculty_id_nb = eoi.employee_id_nb
    innerjoin DWVIEW.dbo.course_section_c as cs
      ON cs.course_section_id = csfr.course_section_id
      andGETDATE() < cs.start_dt
      and cs.status_cd = 'ACTIV';


DECLARE @Emp_Teaching_Last TABLE(
    employee_id_nb varchar(30)
    )
INSERTINTO @Emp_Teaching_Last
SELECTdistinct eoi.employee_id_nb
    from @Emp_Of_Interest as eoi
    innerjoin DWVIEW.dbo.course_Section_faculty_rel_c as csfr
      ON csfr.faculty_id_nb = eoi.employee_id_nb
    innerjoin dwview.dbo.course_section_c as cs
      ON cs.course_section_id = csfr.course_section_id
      andGETDATE() - 90 between cs.start_dt and cs.end_dt
      and cs.status_cd = 'ACTIV';


INSERTINTO @Emp_Teaching_Last_Not_Now
SELECT etlast.employee_id_nb
    from @Emp_Teaching_Last as etlast
    leftouterjoin @EMP_Teaching_Later etl
      ON etlast.employee_id_nb = etl.employee_id_nb
    where etl.employee_id_nb isnullAND etlast.employee_id_nb NOTIN (Select employee_id_nb FROM @EMP_Teaching_Now);
ReturnEND
Tom

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter