Create View - Declare a variable
You can't declare variables in a view. Could you make it into a function or stored procedure?
Edit - you might also be able to put something into a CTE (Common Table Expression) and keep it as a view.
e.g.
WITH conditions as
(
... do the STUFF here
)
SELECT blah
FROM blah
INNER JOIN conditions
(or CROSS JOIN conditions if its just one row, I can't quite decipher what your data is like)
Here is a sample query that uses a CTE (Common Table Expression) to nicely emulate internal variable construction, as described by James Casey. You can test-run it in your version of SQL Server.
CREATE VIEW vwImportant_Users AS
WITH params AS (
SELECT
varType='%Admin%',
varMinStatus=1)
SELECT status, name
FROM sys.sysusers, params
WHERE status > varMinStatus OR name LIKE varType
SELECT * FROM vwImportant_Users
yielding output:
status name
12 dbo
0 db_accessadmin
0 db_securityadmin
0 db_ddladmin
also via JOIN
WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name
FROM sys.sysusers INNER JOIN params ON 1=1
WHERE status > varMinStatus OR name LIKE varType
also via CROSS APPLY
WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name
FROM sys.sysusers CROSS APPLY params
WHERE status > varMinStatus OR name LIKE varType