表变量不能用在sql字符串当中?

[骑猪听涛]

如下语句:

declare @aa table(id decimal(24,6),numa nvarchar(1),value decimal(24,6))
declare @sql nvarchar(4000)

set @sql=' insert '+@aa+' select 100 as id ,''0''as numa,0.2 as value union select 200,''0'' ,0.3 union select 300,''0'',0.4'

exec (@sql)

提示,必须声明@aa错误,为什么?

' select 100 as id ,''0''as numa,0.2 as value union select 200,''0'' ,0.3 union select 300,''0'',0.4'这是为了测试用写成固定的了,实际应用当中是动态的字符串:
set @sql=' insert '+@aa+' +@fstr'

如果要达到我想的效果,该怎么做?

[dawugui]

用正式表或临时表.

[Corn1]

你把这个@aa写在字符串里，这个@aa总得是varchar吧？不然，你这个@sql这个字符串怎么组合？

[dawugui]

你的数据都是常量,不需要使用动态SQL.

DECLARE @TableVar table(id decimal(24,6),numa nvarchar(1),value decimal(24,6)) 

insert into @TableVar 
select 100 as id ,'0'as numa,0.2 as value  
union 
select 200,'0' ,0.3 
union 
select 300,'0',0.4
select * from @TableVar
        
/*
id                         numa value                      
-------------------------- ---- -------------------------- 
100.000000                 0    .200000
200.000000                 0    .300000
300.000000                 0    .400000

（所影响的行数为 3 行）
*/

[子陌红尘]

此处不能用表比那两，而且及时是用表变量，也不是如楼主那般。

create table #(id decimal(24,6),numa nvarchar(1),value decimal(24,6)) 
declare @sql nvarchar(4000) 
set @sql=' insert # select 100 as id ,''0''as numa,0.2 as value  union select 200,''0'' ,0.3 union select 300,''0'',0.4' 
exec  (@sql) 
select * from #

[lgxyz]

因为是临时表而已
所以在要么就是真实的表或是临时表

你要定义变量表只能在那动态里一起定义

[Corn1]

还有，你这个@sql里面没有定义这个@aa这个变量。你定义在外面有啥用

[x44348428]

白猪，这么简单的问题还问！

[华芸智森]

CREATE TYPE SwapTab AS TABLE --定义
( 
	F1 VARCHAR(32) NULL,
	F2 VARCHAR(32) NULL 
) 

 GO

CREATE PROCEDURE SWAPTAB_TEST 
( 
	@TAB SwapTab READONLY --声明一个表值变量.
) 
AS 
BEGIN 
	SELECT * FROM @TAB
END 

GO 

declare @TAb_1 as SwapTab --声明表值变量.
insert into @TAb_1 (f1,f2) values(1,2)
execute SWAPTAB_TEST @TAb_1 --传入表值变量.

[ks_reny]

[Quote=引用楼主 lovewjm 的帖子:]
如下语句:

declare @aa table(id decimal(24,6),numa nvarchar(1),value decimal(24,6))
declare @sql nvarchar(4000)

set @sql=' insert '+@aa+' select 100 as id ,''0''as numa,0.2 as value union select 200,''0'' ,0.3 union select 300,''0'',0.4'

exec (@sql)

提示,必须声明@aa错误,为什么?

' select 100 as id ,''0''as numa,0.2 as value union select 200,''0'' ,0.3 union select …
[/Quote]
樓主用臨時表吧

create  table #a(id decimal(24,6),numa nvarchar(1),value decimal(24,6)) 
declare @sql nvarchar(4000) 
set @sql=' insert into #a  select 100 as id ,''0''as numa,0.2 as value  union select 200,''0'' ,0.3 union select 300,''0'',0.4'      
exec  (@sql) 
-------------------------------
(3 row(s) affected)