LutzM (8/23/2014)
You need to apply the cast to varchar inside the dynamic SQL. Otherwise you're only converting the dynaic statement, not the result.
declare @result varchar(max)set @result = ('select cast((select * from dbo.t for xml path) as varchar(max))')
print @result
exec (@result)
I read some where.. it should work if we place cast in quotes or with out quotes in dynamic sql .but anyways thanks its working now.
Also one doubt is
set say if we declare a variable
declare @col varchar
set @col = 'indu'
and if we pass this variable to dynamic sql we need to quote it again..why is it so?we have already quotes while declaring variable right?