SQL Server : declare view inside a user-define function

Question

I want to create view inside my function and whenever I would like to create my view I just refer to function and call it.

I wrote this code but I got an error:

create FUNCTION [dbo].[testFunc]()
RETURNS bigint
AS
BEGIN
    IF OBJECT_ID ('dbo.r_Sales01_Requests__Duplicates', 'V') IS NOT NULL
       DROP VIEW dbo.r_Sales01_Requests__Duplicates ;
    go

    create view r_Sales01_Requests__Duplicates ( 
     CompanyID
    ,Branch
    ,Year
    ,VoucherType,VoucherNumber
    ,Date_Persian
    ,Row) as
     select 
         CompanyID
        ,Branch
        ,Year
        ,VoucherType
        ,VoucherNumber
        ,Date_Persian
        ,Row
     from t_SalesRequests
     group by CompanyID, Branch, Year, VoucherType, VoucherNumber, Date_Persian, Row
     having count(*)>1

     return 
END

Note : The below part is very important for me to have it while creating function .

IF OBJECT_ID ('dbo.r_Sales01_Requests__Duplicates', 'V') IS NOT NULL
    DROP VIEW dbo.r_Sales01_Requests__Duplicates ;

You won't be able to create a view inside a function (http://msdn.microsoft.com/en-gb/library/ms191320.aspx). To do so inside a stored procedure you will need to use dynamic SQL. http://stackoverflow.com/questions/7712702/creating-a-view-using-stored-procedure — Laurence, Sep 03 '14 at 19:02
You cannot create sql server objects inside a User define function have a look here to read more about [`Create User-defined Functions Limitations`](http://msdn.microsoft.com/en-gb/library/ms191320.aspx) — M.Ali, Sep 03 '14 at 19:05
***WHY*** do you need a function to create the view?!?!? Just **create the view** in SQL Server Management Studio and use it! No point in constantly dropping and re-creating it... — marc_s, Sep 03 '14 at 19:51

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

Functions aren't supposed to have side effects. You can do something like this with a stored procedure:

Creating a View using stored procedure

edited May 23 '17 at 11:57

Community

1
1

answered Sep 03 '14 at 19:54

quillbreaker

6,119
3
29
47

SQL Server : declare view inside a user-define function

1 Answers1