nick nick - 5 months ago 9
SQL Question

TSQL - Changing Name of Case While Looping In A Stored Proceedure

I am using a stored procedure to add columns to a grid on a Windows Form.

I am having a problem dynamically adding new columns to this grid.

I am using SQL Server 2014 Management Studio.

What I am attempting to do is use a variable declared in SQL to name each column... Here is an example.

@Date as date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cnt as int = 0;
DECLARE @name as varchar(3);
WHILE @cnt < 12
BEGIN
SELECT SUM(Price) as Price, Type,
SUM(CASE WHEN dbo.myTable.Date = @Date THEN dbo.myTable.Price ELSE NULL END) AS @name
FROM myTable
WHERE (Date BETWEEN @Date AND DATEADD(dd, 60, @Date))
GROUP BY Type
SET @cnt = @cnt + 1
SET @name = @name + CONVERT(varchar(1),@cnt)
END;


I am attempting to create a loop and change the name of each case statement as I pull them into the grid but the way I'm implementing it doesn't seem to be working.

Here is what my desired output is...
Click Here

One way I am able to get my desired output is creating multiple case statements...

SELECT SUM(Price) as Price, Type,
SUM(CASE WHEN dbo.myTable.Date = DATEADD(dd,1,@Date) THEN dbo.myTable.Price ELSE NULL END) AS D1,
SUM(CASE WHEN dbo.myTable.Date = DATEADD(dd,2,@Date) THEN dbo.myTable.Price ELSE NULL END) AS D2,
SUM(CASE WHEN dbo.myTable.Date = DATEADD(dd,3,@Date) THEN dbo.myTable.Price ELSE NULL END) AS D3,
...
SUM(CASE WHEN dbo.myTable.Date = DATEADD(dd,60,@Date) THEN dbo.myTable.Price ELSE NULL END) AS D60
FROM myTable
WHERE (Date BETWEEN @Date AND DATEADD(dd, 60, @Date))
GROUP BY Type
END;


I am curious if there is a cleaner way to do this utilizing a loop like I attempted to do in the above code.

Is there a way to implement what I am trying to get done or should I look for another solution?

Thank you.

Answer

the while loop should work for you.

declare 
@x varchar(2) = 1,
@sql varchar(8000),
@Date varchar(30) ='2016-01-01',
@y varchar(2) = 6

while @x < @y
begin

set @sql = isnull(@sql,'')+',SUM(CASE WHEN dbo.myTable.Date = DATEADD(dd,' +@x +' ,'+''''+@Date+''''+') THEN dbo.myTable.Price ELSE NULL END) AS D'+@x + '  '

set @x = @x +1

end 

set @sql = 'SELECT  SUM(Price) as Price '+@sql +' FROM myTable WHERE (Date BETWEEN '+''''+@Date+''''+' AND  DATEADD(dd,'+ @y+',' +''''+@Date+''''+')) '

exec( @sql)

which builds this query

 SELECT SUM(Price) AS Price
    ,SUM(CASE 
            WHEN dbo.myTable.DATE = DATEADD(dd, 1, '2016-01-01')
                THEN dbo.myTable.Price
            ELSE NULL
            END) AS D1
    ,SUM(CASE 
            WHEN dbo.myTable.DATE = DATEADD(dd, 2, '2016-01-01')
                THEN dbo.myTable.Price
            ELSE NULL
            END) AS D2
    ,SUM(CASE 
            WHEN dbo.myTable.DATE = DATEADD(dd, 3, '2016-01-01')
                THEN dbo.myTable.Price
            ELSE NULL
            END) AS D3
    ,SUM(CASE 
            WHEN dbo.myTable.DATE = DATEADD(dd, 4, '2016-01-01')
                THEN dbo.myTable.Price
            ELSE NULL
            END) AS D4
    ,SUM(CASE 
            WHEN dbo.myTable.DATE = DATEADD(dd, 5, '2016-01-01')
                THEN dbo.myTable.Price
            ELSE NULL
            END) AS D5
FROM myTable
WHERE (DATE BETWEEN '2016-01-01' AND DATEADD(dd, 6, '2016-01-01'))