Create/Alter view with declare variable

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.

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

|||

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.