I need a fuction which takes a parameter and then uses a select statement to check if a record already exists. The function returns a bit. If the select statement returns something then I want the function to return true and if the select statement does
not return something then return false. I need help processing the result of the select. Here is the function.
CREATE FUNCTION dbo.ChkDuplEmailD
(
@myemail nvarchar(100)
)
RETURNS bit
AS
BEGIN
SELECT [email]
FROM [dbo].[EmailAgendaD]
where email = @myemail
That should get you what you're looking for. I used Print just test the results of the query, just alter the if statement to return your bits instead of printing the text I put in there
No you can return you bits you just need to modify the logic of what I did to match what you're trying to do :)
CREATE FUNCTION dbo.ChkDuplEmailD
(
@CountRecords Integer, @myemail nvarchar(100)
)
RETURNS bit
AS
BEGIN
Set @CountRecords = (Select Count(*) From [dbo].[EmailAgendaD] where email = @myemail)
If @CountRecords > 0
RETURN 1
else
return 0
END
GO
create function dbo.ChkDuplEmailD (@myEmail nvarchar(100))
returns bit
begin
Declare @CountRecords Integer, @prm_bit bit
Set @CountRecords = (Select Count(*) From [dbo].[EmailAgendaD] where email = @myemail)
If @CountRecords > 0
Set @prm_bit = 1
else
Set @prm_bit = 0
return (@prm_bit)
end
go
I just used this exact same function on my DB with a few modifications to check to see if Customer Service Advisor ID exists in my Database and it works like a champ. That should give you exactly what you're looking for
create function dbo.CSAExists (@CSANo nvarchar(100))
returns bit
begin
Declare @CountRecords Integer, @prm_bit bit
Set @CountRecords = (Select Count(*) From [dbo].CSASalesmen where CSANo = @CSANo)
If @CountRecords > 0
Set @prm_bit = 1
else
Set @prm_bit = 0
return (@prm_bit)
end
go
ALTER FUNCTION dbo.Chkduplemaild (@myemail NVARCHAR(100))
RETURNS BIT
AS
BEGIN
RETURN ( CASE
WHEN EXISTS (SELECT *
FROM [dbo].[EmailAgendaD]
WHERE email = @myemail)
THEN 1
ELSE 0
END )
END
miltonsnider
Member
228 Points
457 Posts
Function to check for duplicate email
Apr 27, 2012 02:01 PM|LINK
I need a fuction which takes a parameter and then uses a select statement to check if a record already exists. The function returns a bit. If the select statement returns something then I want the function to return true and if the select statement does not return something then return false. I need help processing the result of the select. Here is the function.
CREATE FUNCTION dbo.ChkDuplEmailD
(
@myemail nvarchar(100)
)
RETURNS bit
AS
BEGIN
SELECT [email]
FROM [dbo].[EmailAgendaD]
where email = @myemail
If ???
RETURN 1
else
return 0
END
GO
thanks
Milton
Loganix77
Participant
1351 Points
412 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:17 PM|LINK
Loganix77
Participant
1351 Points
412 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:19 PM|LINK
That should get you what you're looking for. I used Print just test the results of the query, just alter the if statement to return your bits instead of printing the text I put in there
miltonsnider
Member
228 Points
457 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:20 PM|LINK
So the function returns either Email Exists or No Email Found ?
Milton
Shankar_ss
Participant
1270 Points
279 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:23 PM|LINK
Can you change your query like this:
IF NOT EXISTS(SELECT [email] FROM [dbo].[EmailAgendaD] where email = @myemail)
RETURN 0
else
return 1
end
Shankar
Loganix77
Participant
1351 Points
412 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:24 PM|LINK
No you can return you bits you just need to modify the logic of what I did to match what you're trying to do :)
CREATE FUNCTION dbo.ChkDuplEmailD ( @CountRecords Integer, @myemail nvarchar(100) ) RETURNS bit AS BEGIN Set @CountRecords = (Select Count(*) From [dbo].[EmailAgendaD] where email = @myemail) If @CountRecords > 0 RETURN 1 else return 0 END GOLoganix77
Participant
1351 Points
412 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:41 PM|LINK
Loganix77
Participant
1351 Points
412 Posts
Re: Function to check for duplicate email
Apr 27, 2012 02:51 PM|LINK
I just used this exact same function on my DB with a few modifications to check to see if Customer Service Advisor ID exists in my Database and it works like a champ. That should give you exactly what you're looking for
create function dbo.CSAExists (@CSANo nvarchar(100)) returns bit begin Declare @CountRecords Integer, @prm_bit bit Set @CountRecords = (Select Count(*) From [dbo].CSASalesmen where CSANo = @CSANo) If @CountRecords > 0 Set @prm_bit = 1 else Set @prm_bit = 0 return (@prm_bit) end golimno
All-Star
117314 Points
7997 Posts
Moderator
MVP
Re: Function to check for duplicate email
Apr 27, 2012 03:16 PM|LINK
ALTER FUNCTION dbo.Chkduplemaild (@myemail NVARCHAR(100)) RETURNS BIT AS BEGIN RETURN ( CASE WHEN EXISTS (SELECT * FROM [dbo].[EmailAgendaD] WHERE email = @myemail) THEN 1 ELSE 0 END ) ENDFormat your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm