Create View - Declare a variable
Solution 1
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)
Solution 2
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
Solution 3
Try put the condition subquery directly inside the the view select statement. you may CAST the XML to VARCHAR(20).
CREATE VIEW [AQB_OB].[GISREQUESTEDBURNS]
AS
SELECT RB.[RequestedBurnsID] AS REQUESTEDBURNID
,BUY.[BurnYear] AS BURNYEAR
,CY.[CurrentYear] AS CURRENTYEAR
,RB.[BurnSitesID] AS BURNSITESID
,[BurnerID] AS BURNERID
,[Contact] AS CONTACT
,[BurnDecision] AS BURNDECISION
,RB.[Comment] AS COMMENT,
(
SELECT DISTINCT BD.[RequestedBurnsID],
[ConditionsReasonsID] = STUFF((SELECT ', ' + CONVERT(VARCHAR (20), [ConditionsReasonsID]) FROM [AQB_OB].[BurnDecisions]
WHERE [RequestedBurnsID]= BD.[RequestedBurnsID] ORDER BY [RequestedBurnsID] ASC
FOR XML PATH ('')) , 1 , 1, '') FROM
[AQB_OB].[BurnDecisions] BD
) AS CONDITIONS
FROM [AQB_MON].[AQB_OB].[RequestedBurns] RB
LEFT join AQB_MON.[AQB_OB].[PileDryness] PD on RB.[PileDrynessID] = PD.[PileDrynessID]
inner join AQB_MON.[AQB_OB].[BurnYear] BUY on BUY.BurnYearID = BP.BurnYearID
inner join AQB_MON.[AQB_OB].[CurrentYear] CY on CY.CurrentYearID = BUY.CurrentYearID
Solution 4
Or use a CTE (common table expression) as subselect like:
WITH CTE_Time(Clock)
AS(
SELECT 11 AS [Clock] -- set var
)
SELECT
DATEPART(HOUR, GETDATE()) AS 'actual hour',
CASE
WHEN DATEPART(HOUR, GETDATE()) >= (SELECT [Clock] FROM CTE_Time) THEN 'after'
ELSE 'before'
END AS [Data]
Ethel Patrick
Updated on July 29, 2022Comments
-
Ethel Patrick almost 2 years
I am creating a view that is using that
STUFF
function. I want to put the result ofSTUFF
in a variable for my view. The problem I am having is declaring my variable. It gives me the message "Incorrect Syntax near 'DECLARE'. Expecting '(' or SELECT." I already have the '(' in there. I have tried putting aBEGIN
before it. I have tried putting it after theSELECT
word. But nothing seems to work and I cannot find a solution in my search. I am using SQL Server 2012CREATE VIEW [AQB_OB].[GISREQUESTEDBURNS] AS (DECLARE @CONDITIONS AS varchar(20) SET @CONDITIONS = (SELECT DISTINCT BD.[RequestedBurnsID] ,[ConditionsReasonsID] = STUFF((SELECT ', ' + CONVERT(VARCHAR (20),[ConditionsReasonsID]) FROM [AQB_OB].[BurnDecisions] WHERE [RequestedBurnsID]= BD.[RequestedBurnsID] ORDER BY [RequestedBurnsID] ASC FOR XML PATH ('')) , 1 , 1, '') FROM [AQB_OB].[BurnDecisions] BD) SELECT RB.[RequestedBurnsID] AS REQUESTEDBURNID ,BUY.[BurnYear] AS BURNYEAR ,CY.[CurrentYear] AS CURRENTYEAR ,RB.[BurnSitesID] AS BURNSITESID ,[BurnerID] AS BURNERID ,[Contact] AS CONTACT ,[BurnDecision] AS BURNDECISION ,RB.[Comment] AS COMMENT ,@CONDITIONS AS CONDITIONS FROM [AQB_MON].[AQB_OB].[RequestedBurns] RB LEFT join AQB_MON.[AQB_OB].[PileDryness] PD on RB.[PileDrynessID] = PD.[PileDrynessID] inner join AQB_MON.[AQB_OB].[BurnYear] BUY on BUY.BurnYearID = BP.BurnYearID inner join AQB_MON.[AQB_OB].[CurrentYear] CY on CY.CurrentYearID = BUY.CurrentYearID GO