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.

Answer 1

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

Answer 2

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.