Can i call a stored procedure through a view in Microsoft SQL Azure (RTM) - 12.0.2000.8

Question

I have a dynamic pivot stored proc which needs to be called from view, is it possible in Microsoft SQL Azure (RTM) - 12.0.2000.8?

CREATE PROCEDURE [dbo].[MyProc]
AS
DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX)

SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(DIVISION) from [dbo].[sales_table] GROUP BY DIVISION

IF ISNULL(@pivv, '')<>''
    SET @Query='SELECT * FROM(
        SELECT STOREID, DIVISION  TYP,   SALES VAL from [dbo].[sales_table] 
        )x pivot (MAX(VAL) for TYP in ('+@pivv+')) as xx'

IF ISNULL(@Query, '')<>''
EXEC (@Query)
GO

Answer 1

Here is the answer :

when below proc is executed, it creates a view directly and this proc can be triggered through an elastic job infrequent intervals or by checking whenever a new division is added to the source table.

CREATE PROCEDURE [dbo].[MyProc]
AS
DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX)

SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(DIVISION) from [dbo].[sales_table] GROUP BY DIVISION

IF ISNULL(@pivv, '')<>''
    SET @Query='CREATE VIEW MY_VIEW AS SELECT * FROM(
        SELECT STOREID, DIVISION  TYP,   SALES VAL from [dbo].[sales_table] 
        )x pivot (MAX(VAL) for TYP in ('+@pivv+')) as xx'

IF ISNULL(@Query, '')<>''
EXEC (@Query)
GO