Casting a dynamic Sql

Question

Post reply

Casting a dynamic Sql

Priya004

Ten Centuries

Points: 1368
More actions
August 23, 2014 at 1:36 pm

#310364

Here im trying to cast the dynamic sql into varchar .when I run the below is still displaying xml result set only.
It is not casting to varchar.I need the below as UI will read xml tags
declare @result varchar(max)
set @result = (select cast(('select * from '+@servername+'.dbname.dbo.test for xml path ') as varchar(max))
print @result
exec (@result)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What do you mean by "It is not casting to varchar"?

Do you get any error message?

Can you provide a test scenario so we can reproduce the result you're getting?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Priya004 Ten Centuries Points: 1368 More actions · Answer 2

LutzM (8/23/2014)
What do you mean by "It is not casting to varchar"?
Do you get any error message?
Can you provide a test scenario so we can reproduce the result you're getting?

create table t

(

col1 varchar(10)

)

insert into t values('a')

insert into t values('b')

select * from t for xml path

IF u run the above query an xml result will be displayed .i want to convert this to varchar.if my code would have worked correctly ,then a link in the result will not appear.

Let me know if im clear

LutzM SSC Guru Points: 107049 More actions · Answer 3

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)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Priya004 Ten Centuries Points: 1368 More actions · Answer 4

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?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Indu-649576 (8/23/2014)
Here im trying to cast the dynamic sql into varchar .when I run the below is still displaying xml result set only.
It is not casting to varchar.I need the below as UI will read xml tags
declare @result varchar(max)
set @result = (select cast(('select * from '+@servername+'.dbname.dbo.test for xml path ') as varchar(max))
print @result
exec (@result)

Quick thought, try this

😎

DECLARE @servername NVARCHAR(128) = N'localhost';

DECLARE @SQL_STR NVARCHAR(MAX) = N'SELECT CONVERT(VARCHAR(MAX),(SELECT * FROM ' + @servername + N'.dbname.dbo.test FOR XML PATH),1)';

print @SQL_STR

exec (@SQL_STR)

If you need to pass a parameter then use sp_executesql

DECLARE @PARAM_STR NVARCHAR(MAX) = N'@MY_INT INT';

DECLARE @MY_INT INT = 1;

DECLARE @servername NVARCHAR(128) = N'localhost';

DECLARE @SQL_STR NVARCHAR(MAX) = N'SELECT CONVERT(VARCHAR(MAX),(SELECT * FROM ' + @servername + N'.dbname.dbo.test WHERE mycol = @MY_INT FOR XML PATH),1)';

print @SQL_STR

exec sp_executesql @SQL_STR @PARAM_STR, @MY_INT;