February 11, 2008 at 12:06 am
Hi Good morning to all,
I have one table called "TestData":
create table TestData
(
data1 int,
data2 int,
yearPart int
)
Data in this table:
-------------------
insert into TestData values(5,5,2005)
insert into TestData values(6,6,2006)
insert into TestData values(7,7,2007)
I want to partition "TestData" based on column:"YearPart".
for this I've created three views:
create view Year_2005 as
select * from TestData where YearPart = 2005
create view Year_2006 as
select * from TestData where YearPart = 2006
create view Year_2007 as
select * from TestData where YearPart = 2007
Now the requiremment is:
User sends Year number from stored procedure as a parameter.
The corresponding View has to execute. How to do this?
I've created the following stored procedure to do this job, but showing compile time error. How to solve this problem?
CREATE PROCEDURE getData @YearPart INT AS
DECLARE
@DateVar VARCHAR(10)
SET @TblDateVar = 'Year_' + CONVERT(CHAR(4),@YearPart)
SELECT * FROM CONVERT(TABLE,@TblDateVar)
February 11, 2008 at 10:11 am
You could partition the tables so that Query Optimizer can pick which table to go against depending on the value of the year. For each year, create a new table with a CHECK constraint on the year value.
Then create a view which is a union of all the year tables. Lastly create a Procedure that gets called with a parameter for the year you want to pull data from.
create table Testyr1 -- table for year 2000
(
data1 int,
data2 int,
yearPart int constraint ck_year_2000 check (yearpart=2000)
)
insert into testyr1
values(1,1,2000)
create table Testyr2 -- table for year 2001
(
data1 int,
data2 int,
yearPart int constraint ck_year_2001 check (yearpart=2001)
)
insert into testyr2
values(1,1,2001)
go
create view whatyear -- create view as UNION
as
select data1, data2, yearpart
from TestYR1
UNION ALL
Select data1,data2, yearpart
from TestYR2
go
create procedure getyear @year int -- create procedure to get records for a year
as
select * from whatyear where yearpart = @year
go
exec getyear @year=2001 -- call the procedure
exec getyear @year=2000
Let me know if you have any questions.
Toni
February 11, 2008 at 12:25 pm
toniupstny (2/11/2008)
You could partition the tables so that Query Optimizer can pick which table to go against depending on the value of the year. For each year, create a new table with a CHECK constraint on the year value.Then create a view which is a union of all the year tables. Lastly create a Procedure that gets called with a parameter for the year you want to pull data from.
...
If you want that to be a partitioned view, the yearPart must be part of the primary key of the table.
May 19, 2008 at 9:25 am
Hi,
How to convert one datatype to another datatype in a table ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply