sql if else

Question

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

Answer 1

CREATE procedure dpt
@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'

Answer 2

hi: thanks for your answer it s working fine

Answer 3

can you give some more information. Are you passing two values (parameters) to the stored procedure one is EID and another ENAME? or only one value? If you are passing two values then in your first & third condition you did not check by EID. In second condition you are checking only with EID. How it is possible? Please clarify your requirement