SQLTeam.com | Weblogs | Forums

Creating a View in SQL code that has Declare statements

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)

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.

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)

1 Like

I tried that but no luck. Thanks, though!

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!