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

[ytt900720]

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

declare @store nvarchar(10)
declare stofile cursor  for
select distinct imsul_store from maxmast.imsul where imsul_store not like '%F' order by 1

open stofile

FETCH NEXT FROM stofile
INTO @store
WHILE @@FETCH_STATUS = 0 
begin

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.imsul
where imsul_store like '''+@store+'%''
group by imsul_item, imsul_store,imsul_account,imsul_bin
order by imsul_bin


select * from #'+@store+'	
')

FETCH NEXT FROM stofile
INTO @store

end
close stofile
DEALLOCATE stofile

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

[ytt900720]

[Quote=引用 6 楼 sql77 的回复:]
引用 5 楼 ytt900720 的回复:
引用 4 楼 sql77 的回复:
'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsul
where imsul_store like '''+@store+'%'''
你这只是查出以+@store开头……
[/Quote]

数据是没有问题的,改成上面的也查不出来.

[kxsoft88]

收藏，谢谢

[ytt900720]

declare @store nvarchar(10)
set @store ='AM'
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.imsul
where imsul_store ='''+@store+''' or imsul_store ='''+@store+'F''
group by imsul_item, imsul_store,imsul_account,imsul_bin
order by imsul_bin
select * from #'+@store+'
')

改成这样是可以查出来的.是不是游标出问题了...求解!

[ytt900720]

[Quote=引用 2 楼 dawugui 的回复:]
在游标中动态SQL?
试试


SQL code
exec ('CREATE TABLE #'+@store+'
(
[serno] [int] IDENTITY (1, 1) NOT NULL ,
[item] [nvarchar] (15) ,
[store] [nvarchar] (10),
[account] nvarchar(10),……
[/Quote]

这样是没有问题能查出来..游标出问题了,求解!!

[SQLCenter]

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  for
select distinct imsul_store from maxmast.imsul where imsul_store not like '%F' order by 1

open stofile

FETCH NEXT FROM stofile
INTO @store
WHILE @@FETCH_STATUS = 0 
begin

truncate table #
/*print*/ exec ('INSERT INTO #
select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsul
where imsul_store like ''%'+@store+'%''
group by imsul_item, imsul_store,imsul_account,imsul_bin
order by imsul_bin')

select * from #

FETCH NEXT FROM stofile
INTO @store

end
close stofile
DEALLOCATE stofil

[ytt900720]

declare @store nvarchar(10)
declare stofile cursor  for
select distinct rtrim(imsul_store) as store from maxmast.imsul where imsul_store not like '%F' order by 1

open stofile

FETCH NEXT FROM stofile
INTO @store
WHILE @@FETCH_STATUS = 0 
begin

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.imsul
where imsul_store='''+@store+''' or imsul_store ='''+@store+'F''
group by imsul_item, imsul_store,imsul_account,imsul_bin
order by imsul_bin

insert into #t1
select * from #'+@store+'	
')

FETCH NEXT FROM stofile
INTO @store

end
close stofile
DEALLOCATE stofile

谢谢各位大虾..问题已经解决.在定义游标的时候没有去空格,所有like 后就变成了@store %......

[dawugui]

表名和查询的内容是一样的吗?

[dawugui]

在游标中动态SQL?
试试

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.imsul
where imsul_store like '''+@store+'%'''
group by imsul_item, imsul_store,imsul_account,imsul_bin
order by imsul_bin


select * from #'+@store+')

[ytt900720]

[Quote=引用 1 楼 dawugui 的回复:]
表名和查询的内容是一样的吗?
[/Quote]
是一样的.都是在同一张表里边的查询.

select distinct imsul_store from maxmast.imsul where imsul_store not like '%F' order by 1

imsul_store like '''+@store+'%''
只有两个值, 就是上面语句排除以F结尾的!

[SQL77]

'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsul
where imsul_store like '''+@store+'%'''
你这只是查出以+@store开头的东西,确定你那两条数据是以+@store这个开头吗

[ytt900720]

[Quote=引用 4 楼 sql77 的回复:]
'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsul
where imsul_store like '''+@store+'%'''
你这只是查出以+@store开头的东西,确定你那两条数据是以+@store这个开头吗
[/Quote]

有的example: AM,AMF.

[SQL77]

[Quote=引用 5 楼 ytt900720 的回复:]
引用 4 楼 sql77 的回复:
'select imsul_item,imsul_store,imsul_account,imsul_bin,sum(imsul_balance) as qty from maxmast.imsul
where imsul_store like '''+@store+'%'''
你这只是查出以+@store开头的东西,确定你那两条数据是以+@store这个……
[/Quote]
语句没错,请检查数据问题

select imsul_item,imsul_store,imsul_account,imsul_bin
from maxmast.imsul
where imsul_store like ''%'+@store+'%''

改成这样看能查出来没有