SQLTeam.com | Weblogs | Forums

Creating a View in SQL code that has Declare statements


#1

The code below works great where I have months (Allocmonth) that vary each time it's created. My problem is that instead of the results being presented, I want the results in a View, which SQL apparently does not allow. Any suggestions?

DECLARE @columns VARCHAR(max)
DECLARE @TotalColumns VARCHAR(max)

SELECT @columns = COALESCE(@columns + ',
[' + cast(ALLOCMONTH as varchar) + ']',
'[' + cast(ALLOCMONTH as varchar)+ ']')

FROM PIVOTTESTVIEW
GROUP BY ALLOCMONTH

ORDER BY ALLOCMONTH

DECLARE @query VARCHAR(max)
SET @query = 'SELECT *
FROM PIVOTTESTVIEW
PIVOT (SUM(AMOUNT) FOR ALLOCMONTH IN
('+ @columns +')) AS TOTALAMOUNT'

EXECUTE(@query)


#2

Afaik, there is no way to create a view that has dynamic column definitions. I don't see how SQL could ever accurately parse such a view.


#3

For a query which allows variable parameters you need a Stored Procedure instead of a View. Would that do the trick? (A stored procedure allows optional parameters to be passed, a bit like a Function)


#4

I tried that but no luck. Thanks, though!


#5

If you post the code that you tried I'm sure someone were can help with sorting it out. Executing Dynamic SQL is can be a bit of a black art!