declare @Str nvarchar(4000), @table varchar(50) Set @Str='' Declare curtable cursor for select name from sysobjects where name like 'wosno%' Open curtable Fetch Next from curtable Into @table While @@Fetch_Status=0 Begin If @Str='' Set @Str='select ISNO,WONO From '+@table else Set @Str=@Str+ 'Union select ISNO,WONO From '+@table Fetch Next from curtable Into @table End Close curtable Deallocate curtable
exec(@Str)
select * from ( select * from WOSNO41 union select * from WOSNO42 union select * from WOSNO43 union select * from WOSNO47 )a where ISNO=...
TO WangZWang(阿来): 比如我只要一条纪录(ISNO=GFU2227)呢?WHERE 应该怎么加?
改成这样通不过 declare @Str nvarchar(4000), @table varchar(50) Set @Str='' Declare curtable cursor for select name from sysobjects where name like 'wosno%' Open curtable Fetch Next from curtable Into @table While @@Fetch_Status=0 Begin If @Str='' Set @Str='select * from (select ISNO,WONO From '+@table else Set @Str=@Str+ ' Union select ISNO,WONO From '+@table Fetch Next from curtable Into @table End Close curtable Deallocate curtable exec(@Str+') a where a.isno='gfu2227'')ERROR: Server: Msg 170, Level 15, State 1, Line 22 Line 22: Incorrect syntax near 'gfu2227'.
exec(@Str+') a where a.isno='gfu2227'')不是这样吧!! 改为: exec(@Str+') a where a.isno='''gfu2227'''')
CREATE PROCEDURE up_Sale (@ISNo nvarchar(20)) AS declare @Str nvarchar(4000), @table varchar(50) begin Set @Str='' Declare curtable cursor for select name from sysobjects where name like 'wosno%' Open curtable Fetch Next from curtable Into @table While @@Fetch_Status=0 Begin If @Str='' Set @Str='select ISNO,WONO From ' + @table + ' where ISNO = '+ @ISNO else Set @Str=@Str+' Union select ISNO,WONO From '+ @table + ' where ISNO =' + @ISNO Fetch Next from curtable Into @table End Close curtable Deallocate curtable exec(@Str ) end GO
不好意思,应改为 exec(@Str+ ') a where a.isno=''gfu2227''')
@table varchar(50) Set @Str=''
Declare curtable cursor for
select name from sysobjects where name like 'wosno%' Open curtable
Fetch Next from curtable Into @table
While @@Fetch_Status=0
Begin
If @Str=''
Set @Str='select ISNO,WONO From '+@table
else
Set @Str=@Str+
'Union select ISNO,WONO From '+@table
Fetch Next from curtable Into @table
End
Close curtable
Deallocate curtable
exec(@Str)
(
select * from WOSNO41
union
select * from WOSNO42
union
select * from WOSNO43
union
select * from WOSNO47
)a where ISNO=...
比如我只要一条纪录(ISNO=GFU2227)呢?WHERE 应该怎么加?
declare @Str nvarchar(4000),
@table varchar(50) Set @Str=''
Declare curtable cursor for
select name from sysobjects where name like 'wosno%' Open curtable
Fetch Next from curtable Into @table
While @@Fetch_Status=0
Begin
If @Str=''
Set @Str='select * from (select ISNO,WONO From '+@table
else
Set @Str=@Str+
' Union select ISNO,WONO From '+@table
Fetch Next from curtable Into @table
End Close curtable
Deallocate curtable
exec(@Str+') a where a.isno='gfu2227'')ERROR:
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near 'gfu2227'.
改为: exec(@Str+') a where a.isno='''gfu2227'''')
AS
declare @Str nvarchar(4000),
@table varchar(50)
begin
Set @Str=''
Declare curtable cursor for
select name from sysobjects where name like 'wosno%'
Open curtable
Fetch Next from curtable Into @table
While @@Fetch_Status=0
Begin
If @Str=''
Set @Str='select ISNO,WONO From ' + @table + ' where ISNO = '+ @ISNO
else
Set @Str=@Str+' Union select ISNO,WONO From '+ @table + ' where ISNO =' + @ISNO
Fetch Next from curtable Into @table
End
Close curtable
Deallocate curtable
exec(@Str )
end
GO
exec(@Str+ ') a where a.isno=''gfu2227''')
execute up_Sale '''gfu222b'''--drop procedure up_Sale