Avatar of dshi15
dshi15Flag for United States of America asked on

how to use sql exists function

Hi Expert,

I have function

Create Function PhoneExists

@Phoneno
@personId

If @Phoneno Exists (Select Phoneno from phonebook where personid=@personId

Return 1

Else

return 0

How to make it work.

thanks in advance
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
hyphenpipe

8/22/2022 - Mon
SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dshi15

the error said

The last statement included within a function must be a return statement.
Guy Hengel [angelIII / a3]

ok, let's see:
Create Function PhoneExists(@Phoneno varchar(50), @personid int)
returns bit
as
begin
 declare @res bit
  if exists (Select null from phonebook where personid=@personId and Phoneno = @phoneno)
    set @res = 1
  Else
    set @res = 0

  return @res
end

Open in new window

ASKER CERTIFIED SOLUTION
hyphenpipe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dshi15

thank you very much,

But my function have error

Msg 156, Level 15, State 1, Procedure HasFeatureAcess, Line 7
Incorrect syntax near the keyword 'AS'.
Msg 178, Level 15, State 1, Procedure HasFeatureAcess, Line 21
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Procedure HasFeatureAcess, Line 24
A RETURN statement with a return value cannot be used in this context.



Create Function HasFeatureAcess
(
      @FeatureID  INT,
      @iMaxRoleId INT
)
AS

RETURNS BIT

BEGIN



IF  EXISTS (SELECT DISTINCT tFeature.iFeatureID
      FROM tFeature
      INNER JOIN tSection ON tFeature.iSectionID = tSection.iSectionID
      INNER JOIN tPermission ON tFeature.iFeatureID = tPermission.iFeatureID
      WHERE tPermission.iRoleID = 2 AND tFeature.iFeatureID=12345)

      Return 1
      

      Return 0

END


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
hyphenpipe

You had the return in the wrong place.


Create Function HasFeatureAcess
(
      @FeatureID  INT,
      @iMaxRoleId INT
)
RETURNS BIT
AS



BEGIN



IF  EXISTS (SELECT DISTINCT tFeature.iFeatureID
      FROM tFeature
      INNER JOIN tSection ON tFeature.iSectionID = tSection.iSectionID
      INNER JOIN tPermission ON tFeature.iFeatureID = tPermission.iFeatureID
      WHERE tPermission.iRoleID = 2 AND tFeature.iFeatureID=12345)

      Return 1
      

      Return 0

END

Open in new window

ASKER
dshi15

Thank you very much.

Do I need use 'null ' in select, what that means?

IF  EXISTS (SELECT null      FROM tFeature


or

IF  EXISTS (SELECT DISTINCT tFeature.iFeatureID
      FROM tFeature

hyphenpipe

Either one will work fine.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dshi15

Could explain what means select null?
hyphenpipe

The where exists just looked for the at least one row to be returned, the data returned is irrelevant, so the null was just a placeholder of sorts.