CODEWITHSUNDEEP
t-sql
Nhu Nguyen
Nhu Nguyen

Reputation: 874

Variable in T-SQL

I have the code T-SQL as follows:

declare @xml xml = N'
<a abb="122">
    <b>
    </b>
</a>
<a abb="344">
    <b>
    </b>
</a>';

declare @T table (XMLCol xml);

insert into @T values (@xml);

declare @sql varchar(max);
set @sql = 'update @T set
                     XMLCol.modify(''
                            replace value of (/a/@abb)[1] 
                            with 888'');';
exec (@sql);

When I execute, a fault segment appears:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@T".

How to declare variable @T to be understand?

Upvotes: 2

Views: 894

Answers (6)

Shailesh Mistry
Shailesh Mistry

Reputation: 1

declare @xml xml = N'
<a abb="122">
    <b>
    </b>
</a>
<a abb="344">
    <b>
    </b>
</a>';

declare @T table (XMLCol xml);

insert into @T values (@xml);


select * from @t
update @T set XMLCol.modify('replace value of (/a/@abb)[1] with 888');
select * from @t

This will work for your result

Upvotes: 0

pcofre
pcofre

Reputation: 4066

Because you need to declare everything inside the dynamic part of the query. In your case i still don´t understand why you need the dynamic query, but if it´s necesary it should look like:

declare @sql varchar(max);
set @sql = '
declare @xml xml = N''
<a abb="122">
    <b>
    </b>
</a>
<a abb="344">
    <b>
    </b>
</a>'';

declare @T table (XMLCol xml);
insert into @T values (@xml);
update @T set
                     XMLCol.modify(''
                            replace value of (/a/@abb)[1] 
                            with 888'');';
exec (@sql);

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300489

You cannot use the EXEC statement or sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or sp_executesql stored procedure.

Use a temp table instead.

Upvotes: 1

hallie
hallie

Reputation: 2845

Because your @sql is executed on a different execution block so it will not find it. You need to declare your @t inside the "@sql" variable, including all transactions related to your @t.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use a temp table instead of table variable.

declare @xml xml = N'
<a abb="122">
    <b>
    </b>
</a>
<a abb="344">
    <b>
    </b>
</a>';

create table #T (XMLCol xml);

insert into #T values (@xml);

declare @sql varchar(max);
set @sql = 'update #T set
                     XMLCol.modify(''
                            replace value of (/a/@abb)[1] 
                            with 888'');';
exec (@sql);

drop table #T

Upvotes: 1

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

Table variables are not seen in the inner SPs so you should define a temporary table there not a table variable.

Upvotes: 0

Related Questions