December 14, 2012 at 5:59 am
HERE IAM HAVING A SAMPLE TABLE
declare @T table
(
Name char(1),
HEIGHT deCIMAL(18,2)
)
insert into @T values
('a',17.2 ),
('b', 17.8),
('c', 3.8),
('d',4.2),
('e',3.2)
select Name
from @T
FOR THIS TABLE I WANT TO WRITE PROC WITH 2 PARAMETER TO FIND NAME S WHICH IS HAVING HEIGHT
declare @FROM deCIMAL(18,2)
declare @TO deCIMAL(18,2)
IF @FROM=0
BEGIN
SET @FROM=NULL
END
IF @TO=0
BEGIN
SET @TO=NULL
END
SELECT NAME
FROM @T
WHERE
( @FROM IS NULL OR HEIGHT=@FROM)
AND
( @TO IS NULL 0R HEIGHT= @TO )
THIS IS HOW TRIED
BUT NEED OUTPUT LIKE THIS WHEN I PASS 2 PARAMETER
declare @FROM deCIMAL(18,2) =3
declare @TO deCIMAL(18,2)=4
OUTPUT LIKE THIS
Name HEIGHT
e 3.20
c 3.80
d 4.20
SIMILARLY WHEN I PASS 2 PARAMETER AS 0 MEANS
declare @FROM deCIMAL(18,2) =0
declare @TO deCIMAL(18,2)=0
OUTPUT LIKE THIS
Name HEIGHT
a 17.20
b 17.80
c 3.80
d 4.20
e 3.20
and i just tried like this also
select Name
from @T
where height between @FROM and @tO
December 14, 2012 at 6:59 am
well, this should work
declare @Tomax decimal(18,2)
set @Tomax=(select max(height) from @T)
set @FROM =isnull(@FROM,0)
set @tO=isnull(@tO, Tomax)
select *
from @T
where height between @FROM and @tO
If you need to round it up then when u select you can do
select *
from @T
where convert(int,height) between @FROM and @tO
December 14, 2012 at 8:14 am
hi dragosgrigs i just made the solution like this and i used floor () to solve it floor (HEIGHT) and thanks alot for giving new idea's dragosgrigs.
declare @T table
(
Name char(1),
HEIGHT deCIMAL(18,2)
)
insert into @T values
('a',17.2),
('b', 17.8),
('c', 3.8),
('d',4.2),
('e',3.2)
select Name,HEIGHT
from @T
and this i the store proc which i made
declare @FROM deCIMAL(18,2) =4
declare @TO deCIMAL(18,2)=17
IF @FROM=0
BEGIN
SET @FROM=NULL
END
IF @TO=0
BEGIN
SET @TO=NULL
END
SELECT NAME,HEIGHT
FROM @T
WHERE
( @FROM IS NULL AND @TO IS NULL )
OR
( FLOOR(HEIGHT) BETWEEN @FROM AND @TO )
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply