DECLARE @SqlWhere varchar(8000) DECLARE @Sql varchar(8000) DECLARE @A TABLE(FBookID int) DECLARE @TCustomerByBook varchar(200) INSERT @A EXEC GetBookIdByCustomer 5073 --5073是存储过程GetBookIdByCustomer的参数(客户ID) SET @SqlWhere='1=1' set @TCustomerByBook='表名' --IF 条件--这里根据条件的判断动态加载 SET @SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM '+@TCustomerByBook+')' Set @Sql = ' .................................... ' print('SELECT * FROM ('+@Sql+') as t ')这样试试
@TCustomerByBook是表变量 不能拿来做组合动态语句的
把表变量换成临时表 create table #(id int)exec('insert # values(1)')select * from # /* id ----------- 1(1 行受影响) */
不好意思 我写错了 @TCustomerByBook 应该是@A DECLARE @SqlWhere varchar(8000) DECLARE @Sql varchar(8000) DECLARE @A TABLE(FBookID int) INSERT @A EXEC GetBookIdByCustomer 5073 --5073是存储过程GetBookIdByCustomer的参数(客户ID) SET @SqlWhere='1=1' --IF 条件--这里根据条件的判断动态加载 SET @SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM '+@A+')' Set @Sql = ' .................................... ' print('SELECT * FROM ('+@Sql+') as t ')
我试了换成临时表还是提示 列名无效 @SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM '+#t+')' @SqlWhere是个字符串
#t直接写在语句里面。 @SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM #t)'
DECLARE @Sql varchar(8000)
DECLARE @A TABLE(FBookID int)
DECLARE @TCustomerByBook varchar(200)
INSERT @A EXEC GetBookIdByCustomer 5073 --5073是存储过程GetBookIdByCustomer的参数(客户ID)
SET @SqlWhere='1=1'
set @TCustomerByBook='表名'
--IF 条件--这里根据条件的判断动态加载
SET @SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM '+@TCustomerByBook+')'
Set @Sql = '
....................................
'
print('SELECT * FROM ('+@Sql+') as t ')这样试试
create table #(id int)exec('insert # values(1)')select * from #
/*
id
-----------
1(1 行受影响)
*/
@TCustomerByBook 应该是@A DECLARE @SqlWhere varchar(8000)
DECLARE @Sql varchar(8000)
DECLARE @A TABLE(FBookID int)
INSERT @A EXEC GetBookIdByCustomer 5073 --5073是存储过程GetBookIdByCustomer的参数(客户ID)
SET @SqlWhere='1=1'
--IF 条件--这里根据条件的判断动态加载
SET @SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM '+@A+')'
Set @Sql = '
....................................
'
print('SELECT * FROM ('+@Sql+') as t ')
@SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM '+#t+')'
@SqlWhere是个字符串
@SqlWhere=@SqlWhere+' AND b.FBookID NOT IN ( SLECT FBookID FROM #t)'