动态sql语句使用匹配查询?解决方法

动态sql语句使用匹配查询??
动态sql语句怎么样使用匹配查询?

SQL code

declare @store nvarchar(10)declare stofile cursor  forselect distinct imsul_store from maxmast.imsul where imsul_store not like '%F' order by 1open stofileFETCH NEXT FROM stofileINTO @storeWHILE @@FETCH_STATUS = 0 beginexec ('CREATE TABLE #'+@store+'(    [serno] [int] IDENTITY (1, 1)  NOT NULL ,    [item] [nvarchar] (15) ,    [store] [nvarchar] (10),    [account] nvarchar(10),    [bin] nvarchar(10),    [qty] float ) ON [PRIMARY]INSERT INTO #'+@store+'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsulwhere imsul_store like '''+@store+'%''group by imsul_item, imsul_store,imsul_account,imsul_binorder by imsul_binselect * from #'+@store+'    ')FETCH NEXT FROM stofileINTO @storeendclose stofileDEALLOCATE stofile

上面的代码哪出问题了,为什么我只能查出imsul_store=@store的记录,不能查出imsul_store =@store%的东西??

[解决办法]
在游标中动态SQL?
试试

SQL code

exec ('CREATE TABLE #'+@store+'(    [serno] [int] IDENTITY (1, 1)  NOT NULL ,    [item] [nvarchar] (15) ,    [store] [nvarchar] (10),    [account] nvarchar(10),    [bin] nvarchar(10),    [qty] float ) ON [PRIMARY]INSERT INTO #'+@store+'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsulwhere imsul_store like '''+@store+'%'''group by imsul_item, imsul_store,imsul_account,imsul_binorder by imsul_binselect * from #'+@store+')
[解决办法]
'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsul
where imsul_store like '''+@store+'%'''
你这只是查出以+@store开头的东西,确定你那两条数据是以+@store这个开头吗
[解决办法]
SQL code
create table #(    [serno] [int] IDENTITY (1, 1)  NOT NULL ,    [item] [nvarchar] (15) ,    [store] [nvarchar] (10),    [account] nvarchar(10),    [bin] nvarchar(10),    [qty] float )declare @store nvarchar(10)declare stofile cursor  forselect distinct imsul_store from maxmast.imsul where imsul_store not like '%F' order by 1open stofileFETCH NEXT FROM stofileINTO @storeWHILE @@FETCH_STATUS = 0 begintruncate table #/*print*/ exec ('INSERT INTO #select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsulwhere imsul_store like ''%'+@store+'%''group by imsul_item, imsul_store,imsul_account,imsul_binorder by imsul_bin')select * from #FETCH NEXT FROM stofileINTO @storeendclose stofileDEALLOCATE stofil 

 

查看更多 下一篇