SQL- Stored Procedure question - thanks

[rayxu]

SQL Database tables (MS SQL 7)

test001002 (001002 = yymmdd)
test001112
test001122
test001123

Note: All the above tables have the same structure and with the 'AccountNo' field.


Input parameter (001123)

Requirement:

display the AccountNo fields of all the above tables if the date (last 6 character of the table name)" is equal to or prior to the "Input parameter (001123) (must be within the same month and same year). In this case, display the 'AccountNo' fields of the following tables

test001112
test001122
test001123

Note: test001002 table is not included because it is October which is not in the same month as the Input parameter (001123--yymmdd).

Note: you can create a tmp table and add AccountNo fields of three tables one after another

Please help me out. thanks

[epelyavski]

Try this:


-- define test tables
create table test001002 (AccountNo int)
create table test001112 (AccountNo int)
create table test001122 (AccountNo int)
create table test001123 (AccountNo int)
create table test001124 (AccountNo int)

-- insert test data
insert into test001002 values(9001002)
insert into test001112 values(9001112)
insert into test001122 values(9001122)
insert into test001123 values(9001123)
insert into test001124 values(9001124)

-- stored procedure
drop procedure ShowAccountNo
go

create procedure ShowAccountNo(@Param char(6))
as

declare @YearMonth char(4),
@TableName varchar(15),
@ExecStr varchar(255)

set @YearMonth = substring(@Param,1,4)

create table #Temp_AccountNo (AccountNo int)

declare Cur_Tbl scroll cursor
for -- select testYYMMXX tables ONLY
select name
from sysobjects
where name like 'test'+@YearMonth+'[0-9][0-9]' and
substring(name,5,6) <= @Param


open Cur_Tbl

fetch first from Cur_Tbl
into @TableName

while @@fetch_status=0
begin
set @ExecStr = 'insert into #Temp_AccountNo select AccountNo from '+@TableName

EXEC (@ExecStr)

fetch next from Cur_Tbl
into @TableName
end

close Cur_Tbl
deallocate Cur_Tbl

select * from #Temp_AccountNo
GO

-- run the procedure
ShowAccountNo '001123'

[rayxu]

Thanks a lot.

One question:

"where name like 'test'+@YearMonth+'[0-9][0-9]' and substring(name,5,6) <= @Param" produces no record
Maybe string can't be compared. Do you know how to convert the string to integer so that they can be compared? I don't know if there is such a function. I used

"WHERE name LIKE "test%" instead of "where name like 'test'+@YearMonth+'[0-9][0-9]' and substring(name,5,6) <= @Param" and it listed some records

Thanks for your help