Create View dynamically from SP in sql server 2012

Question

sql server 2012

i have created a SP to create view dynamically with Parameter i supplied.

below is the code

i am not sure why i am getting the below error.

Msg 102, Level 15, State 1, Procedure uspCreateView, Line 10 Incorrect syntax near '@VIEWNM'.

Code:

CREATE PROCEDURE uspCreateView
@VIEWNM VARCHAR(50),
@COMP INT,
@TODT DATE = 9999-12-31


    AS
    EXEC ('

    CREATE VIEW +'@VIEWNM'
    AS
(
SELECT
COL1,
COL2,
COL3,   
DATE,   
SKU,    
CASE WHEN IsRowCurrent = 1 THEN '@TODT' ELSE dateadd(MILLISECOND, -1000 - datepart(MILLISECOND, FMDT), FMDT) END AS TODT,

FROM MYTABLE mt


OUTER APPLY (   SELECT  MAX(DATEADD(second, -1, FMDT)) TO_DATE 
                        FROM    MYTABLE mt2 
                        WHERE   mt2.COL1 = mt.COL1 
                                AND mt2.COL2 = mt.COL2 
                                AND mt2.FMDT > mt.FMDT
                                AND mt.COL3 = '@COMPID'  ) oa
  WHERE mt.COL3 = '@COMPID'
  )

please help me to fix the below error. Thanks

You should probably use a function instead of a stored procedure and view and write select * from myFunction(@TODT,@COMPID) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 12, 2018 at 9:04
BTW the culture invariant date format is YYYYMMDD. YYYY-MM-DD is affected by the DATEFORMAT parameter and could be interpreted as YYYY-DD-MM if DATEFORMAT is set to MDY — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 12, 2018 at 9:06

PSK · Accepted Answer · 2018-03-12 09:33:03Z

I am not sure, why you want to create views dynamically, ideally views should be created before hand only, and you will be required to fetch the data depending on the condition.

Still if you want to proceed, you should do it proper way to avoid any issues. I can see there are issues with your implementation. Ideally you should make it parameterized, like following sample query to avoid any SQL injection.

There are issues with your provided code also, I tried to fix some of these.

CREATE PROCEDURE uspCreateView 
    (
      @VIEWNM      VARCHAR(50), 
      @COMP        INT, 
      @TODT        DATE = '9999-12-31' 
      )
    AS
    BEGIN
     DECLARE @Query NVARCHAR(MAX) 
     DECLARE @ParmDefinition nvarchar(MAX);  
    SET @Query = N'CREATE VIEW @VIEWNM_I     AS ( SELECT COL1, COL2, COL3, DATE, SKU, 
        CASE WHEN IsRowCurrent = 1 THEN @TODT_I ELSE 
        dateadd(MILLISECOND, -1000 - datepart(MILLISECOND, FMDT)
        , FMDT) END AS TODT, FROM MYTABLE mt OUTER APPLY (   
        SELECT  MAX(DATEADD(second, -1, FMDT)) TO_DATE                         
        FROM    MYTABLE mt2                         
        WHERE   mt2.COL1 = mt.COL1                                 
        AND mt2.COL2 = mt.COL2                                 
        AND mt2.FMDT > mt.FMDT                                 
        AND mt.COL3 = @COMPID_I  ) oa  
        WHERE mt.COL3 = @COMPID_I)' 
    SET @ParmDefinition = N'@VIEWNM VARCHAR(100),@TODT_I DATETIME,@COMP_I INT';  
    EXECUTE sp_executesql @Query,@ParmDefinition, @VIEWNM_I=@VIEWNM,  @COMP_I=@COMP, @TODT_I=@TODT
    END

ravi polara · Accepted Answer · 2018-03-12 09:12:32Z

0

Try this

Command(s) completed successfully.

CREATE PROCEDURE uspCreateView
@VIEWNM VARCHAR(50),
@COMP INT,
@TODT DATE = '9999-12-31'


    AS
    EXEC ('

    CREATE VIEW '+@VIEWNM+'
    AS
(
SELECT
COL1,
COL2,
COL3,   
DATE,   
SKU,    
CASE WHEN IsRowCurrent = 1 THEN '+@TODT+' ELSE dateadd(MILLISECOND, -1000 - datepart(MILLISECOND, FMDT), FMDT) END AS TODT,

FROM MYTABLE mt


OUTER APPLY (   SELECT  MAX(DATEADD(second, -1, FMDT)) TO_DATE 
                        FROM    MYTABLE mt2 
                        WHERE   mt2.COL1 = mt.COL1 
                                AND mt2.COL2 = mt.COL2 
                                AND mt2.FMDT > mt.FMDT
                                AND mt.COL3 = '+@COMP+'  ) oa
  WHERE mt.COL3 = '+@COMP+'
  )  ')

answered Mar 12, 2018 at 9:12

ravi polara

5823 silver badges14 bronze badges

3 Comments

TULSI Over a year ago

Thanks a lot but why i am getting error when excuting EXEC uspCreateView VIEWNM = 'vw_test' COMP= 9999 TODT= '9999-12-31'. Error message: Incorrect syntax near '@COMP'.

ravi polara Over a year ago

wait i check store procedure.

TULSI Over a year ago

Thanks Ravi for your help

Aswani Madhavan · Accepted Answer · 2018-03-12 09:10:19Z

0

It should be
CREATE VIEW ' + @VIEWNM + ' AS...

edited Mar 12, 2018 at 9:10

answered Mar 12, 2018 at 9:03

Aswani Madhavan

8166 silver badges19 bronze badges

3 Comments

TULSI Over a year ago

but when i am trying to execute like EXEC uspCreateView VIEWNM = 'vwtest', COMPID=9999,TODT=''. i am getting error like Incorrect syntax near '9999'.. Please help

TULSI Over a year ago

Thank You Very Much Aswani, Please help me how to do print meassage

Aswani Madhavan Over a year ago

Declare a variable and assign the dynamic query to that variable.Print that

Collectives™ on Stack Overflow

Create View dynamically from SP in sql server 2012

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related