Dynamic SQL Statement

[schafjoe]

Everybody,
I have the following Code that work fine.

What I want to do is when s.Week = 0, then do not place the week statement.

DECLARE @SQLString VarChar(8000)

SELECT @SQLString =
ISNULL(@SQLString + ',', '') + CHAR(13) +
'SUM(CASE ' +
'WHEN Data.Year = ' + CAST(s.Year as VarChar(4)) +
' AND Data.Month = ' + CAST(s.Month as VarChar(2)) +
' AND Data.Week = ' + CAST(s.Week as VarChar(2)) +
' THEN Data.Qty ELSE 0 END)
FROM
(
SELECT TOP 100 PERCENT
dbo.BD.Year,
dbo.BD.Month,
dbo.BD.Week
FROM dbo.BD
) s

 

[twifosp]

I do my dynamic statements with dynamic variables

so you'd create your @sqlString and put your and statements at the end like this:

declare @sweek varchar(100)

if (select s.week from db where conditions) = 0
begin
set @sweek = ''
end
else
begin
set @sweek = 'AND Data.Week = ' + CAST(s.Week as VarChar
(2))'
end


SELECT @SQLString =
ISNULL(@SQLString + ',', '') + CHAR(13) +
'SUM(CASE ' +
'WHEN Data.Year = ' + CAST(s.Year as VarChar(4)) +
' AND Data.Month = ' + CAST(s.Month as VarChar(2)) +
@sweek

so if sweek is 0, it adds nothing, otherwise it adds your statement.

 

[schafjoe]

Found another solution.

DECLARE @SQLString VarChar(8000)

SELECT @SQLString =
ISNULL(@SQLString + ',', '') + CHAR(13) +
'SUM(CASE ' +
'WHEN Data.Year = ' + CAST(s.Year as VarChar(4)) +
' AND Data.Month = ' + CAST(s.Month as VarChar(2)) +
CASE s.Week
WHEN 0 THEN ''
ELSE ' AND Data.Week = ' + CAST(s.Week as VarChar(2))
END +
' THEN Data.Qty ELSE 0 END)
FROM
(
SELECT TOP 100 PERCENT
dbo.BD.Year,
dbo.BD.Month,
dbo.BD.Week
FROM dbo.BD
) s