Problems in Partition table

I have created partition table and view.
with using below code

create table p11(dt int not null check (dt>=10 and dt<20),nam varchar(10) not null)
create table p22(dt int not null check (dt>=20 and dt<30),nam varchar(10) not null)

alter table p11 add constraint pk_p11 primary key(dt)
alter table p22 add constraint pk_p22 primary key(dt)

create view pv1 as
select * from p11 union all select * from p22

insert into pv1 values(11,'aaa')
insert into pv1 values(21,'aaa')


insertion was perfectly done in the appropriate table. when check with execution plan with below code
select * from pv1 where dt=11
scanning is happening only in one table

where as using the variable it was scanning all the tables

declare @dt int
select @dt =11
select * from pv1 where dt=@dt

what might be the problem in that, please give solution.

Why not use dynamic SQL:

declare @dt int, @sql varchar(200)
set @dt = 11
set @sql = 'select * from pv1 where dt=' + CAST(@dt AS VARCHAR(100))

EXEC (@SQL)

Thanks for your Example

we have tried with the same before, but we need in query ( not in dynamic qry). because we have done in many place like this

quote:Originally posted by Adriaan

Why not use dynamic SQL:

declare @dt int, @sql varchar(200)
set @dt = 11
set @sql = 'select * from pv1 where dt=' + CAST(@dt AS VARCHAR(100))

EXEC (@SQL)

If you can run a query using this syntax ...

declare @dt int
select @dt =11
select * from pv1 where dt=@dt

... then you can run a dynamic SQL query too. Not sure what your problem is?