sql查询问题

SQL code

declare @ListId nvarchar(255)
SET    @ListId='SELECT id FROM [table] WHERE id>10
SELECT *
  FROM table  where [id] not in (@ListId)

我这样子写不行！
提示
在将 nvarchar 值 'SELECT id FROM [table] WHERE id>10' 转换成数据类型 int 时失败。
请问要怎么写，因为我要很多个地方用到 SELECT id FROM [table] WHERE id>10所以想提取出来

SQL code

declare @ListId nvarchar(255)
SET    @ListId='SELECT id FROM [table] WHERE id>10
SELECT *
  FROM table  where [id] not in ('+@ListId+')'

SQL code

declare @ListId nvarchar(255)
SET    @ListId='SELECT id FROM [table] WHERE id>10'

EXEC('
SELECT *
  FROM table  where [id] not in ('+@ListId+')')

直接取小于等于10 不就行了。

declare @ListId nvarchar(255)
set @ListId = N''
SET @ListId= SELECT @ListId = @ListId + ',' + convert(nvarchar(10), id) FROM [table] WHERE fenshu>10

declare @sql nvarchar(1000)

set @sql = N'SELECT *
  FROM table where [id] not in (' + @ListId + ')'
print @sql

SQL code

EXEC('
SELECT *
  FROM table  where [id] not in ('+ltrim(@ListId)+')')

SQL code

declare @ListId varchar(255)
SELECT @ListId=isnull(@ListId+',','')+id FROM [table] WHERE id>10
exec('SELECT * FROM table where id not in ('''+@ListId+''')')

--或者直接
select * FROM [table] WHERE id<=10

SQL code

--id是int型的话改下
declare @ListId varchar(255)
SELECT @ListId=isnull(@ListId+',','')+id FROM [table] WHERE id>10
exec('SELECT * FROM table where id not in ('+@ListId+')')

SQL code

declare @ListId nvarchar(255)
SET    @ListId='SELECT id FROM [table] WHERE id>10
--动态执行语句用exec
exec('SELECT * FROM table  where [id] not in ('+@ListId+')')

mark,学习
神马动态sql最讨厌了...

在将 nvarchar 值 'SELECT id FROM [table] WHERE id>10' 转换成数据类型 int 时失败。


你的id是不是有的不能隐式转换成数值型数据

id > CAST(10 AS VARCHAR(10))