table
eid Ename departnam
E1 science 2
E2 maths 3
E3 Null 4
E4 english 1
in above table
i am table 3 columns,
i need 3 queries should be written in scripts
1. when we search ename other than in table it should display like not exists
2. if u search e3 then it should display as default depatment and ename
3. if u search any name in this table then it should display name and departname
please help
Rupesh KahanePosted Jun 14, 2014, 6:20 AM
@ename varchar(50) -- enter users value
as
BEGIN
declare @name varchar(50), -- copy user value into variable
@ifcheck varchar(50), -- to get second charachter from users string
@no int, -- to check character isnumeric by using function ISNUMERIC
@notpresent varchar(50), -- users value does not exists
@count int -- to count the records
set @ifcheck = (select substring(@ename,2,1))
set @no = ISNUMERIC(@ifcheck)
if(@no=1)
BEGIN
set @count =(select count(*) from Dept where EID like @ename)
if(@count = 1)
begin
select * from dept where EID = @ename
END
else
set @notpresent ='not present'
end
else
if(@no = 0)
BEGIN
set @count =(select count(*) from Dept where ename like @ename)
if(@count = 1)
BEGIN
select * from dept where ENAME = @ename
END
else
set @notpresent ='not present'
END
if(@count = 0)
BEGIN
select @notpresent
END
END
--exec dpt @ename = 'maths'
chethan klPosted Jun 16, 2014, 8:47 AM
Rupesh KahanePosted Jun 14, 2014, 4:55 AM