Ethel Patrick Ethel Patrick - 9 days ago 5
SQL Question

Create View - Declare a variable

I am creating a view that is using that

STUFF
function. I want to put the result of
STUFF
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 a
BEGIN
before it. I have tried putting it after the
SELECT
word. But nothing seems to work and I cannot find a solution in my search. I am using SQL Server 2012

CREATE 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

Answer

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 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)
Comments