Dynamic SQL

DECLARE @SQLString NVARCHAR(4000)
DECLARE @MyDate NVARCHAR(50)

DECLARE cwday CURSOR for
Select distinct weekending from #w

OPEN cwday

FETCH NEXT FROM cwday INTO @MyDate

WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM cwday INTO @MyDate
SET @SQLString = N'SELECT MyID, ' + N'Case Weekending when ' +
@MyDate +
CHAR(13) + N' Then MyResult From #w '
END

print @SQLString
EXECUTE sp_executesql @SQLString

CLOSE cwday
DEALLOCATE cwday

I use above SQL code to generate dynamic SQL and would like to
generate

Select MyID,
case Weekending when '2011-1-1' then MyResult,
case Weekending when '2011-1-5' then MyResult,
case Weekending when '2011-2-8' then MyResult
from #w

All the date is from
"Select distinct weekending from #w"

I can not get the result from above dynamic SQL.
Can you please help if I am in the wrong direction?

Your help is great appreciated,

iccsi ( ) writes:
> I use above SQL code to generate dynamic SQL and would like to
> generate
>
> Select MyID,
> case Weekending when '2011-1-1' then MyResult,
> case Weekending when '2011-1-5' then MyResult,
> case Weekending when '2011-2-8' then MyResult
> from #w
>
> All the date is from
> "Select distinct weekending from #w"

You set @SQLString mulitple times in the loop.

You need to initiate it before the loop with "SELECT MyID". You should
add the FROM part after the loop. You also need to handle the comma, so
you don't get one too many.

--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx