none
Create/Alter view with declare variable

    Question

  •  

    Hi all,

    I not sure whether is there a way to create/alter a view with variable declare in the same statement. I encounter some error when I run the below statement.

    Error:

    Server: Msg 156, Level 15, State 1, Procedure Employee_Details , Line 2
    Incorrect syntax near the keyword 'DECLARE'.
    Server: Msg 170, Level 15, State 1, Procedure Employee_Details , Line 16
    Line 16: Incorrect syntax near ')'.


    My SQL Statement

    ALTER VIEW Employee_Details as (
    DECLARE @usr nvarchar(250)
    SET @usr = user
    SELECT 
     E.ID 
     ,E.NAME 
     ,E.DEPARTMENT 
     ,E.JOB_TITLE
     ,E.JOIN_DATE
     ,E.RESIGN_DATE
    FROM 
     EMPLOYEE AS E
    WHERE 
     E.DEPARTMENT = (SELECT *
     FROM GETCURRENTUSER(@usr))
    )

    The GetCurrentUser is a function I have create to get the department that the user can view.

    Please advice. Thanks in advance.

    Monday, January 22, 2007 2:17 AM

Answers

  • Hello,

    You cannot declare variables in view defiunitions.  You can either create your logic in a procedure or modify your view to make use of the in-built user_name() function in order to return filtered results.

    Cheers,
    Rob

    Monday, January 22, 2007 2:39 AM

All replies

  • Hello,

    You cannot declare variables in view defiunitions.  You can either create your logic in a procedure or modify your view to make use of the in-built user_name() function in order to return filtered results.

    Cheers,
    Rob

    Monday, January 22, 2007 2:39 AM
  • Hi Rob,

    Yes, I have try to use another way of doing and I think I have solve the problem.

    Really thanks for your advice, it does give me another idea of doing this. 

     

    Monday, January 22, 2007 6:01 AM