count,if exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count,if exists

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-01-03 : 02:29:40

i have a table having user name,user company nam,and userid.
i am using a stored procedure where an emailid is passed as parameter.
i want to check that if userid entered exists then no entry happens and it count that userid.

do help....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 06:55:40

can you illustrate it with some sample data. if userid exits what do you want to count? you mean number of times it occurs? Seems like its below, but i'm not sure with your explanation


CREATE PROC YourProcNBame
@EmailID varchar(100),
@Occurance int OUTPUT
AS
IF EXISTS (SELECT 1 FROM YourTable WHERE userid=@EmailID)
BEGIN
SELECT @Occurance=COUNT(*)
FROM YourTable
WHERE userid=@EmailID
END
ELSE
PRINT 'No entry'
SET @Occurance=0
END
GO

then call it like this


DECLARE @Cnt int
EXEC youremailidvalue,@Cnt OUTPUT

SELECT @Cnt--this gives occurance value

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-01-04 : 23:52:59

i forgot to tell that i have a cloumn of emailid also in my table....
the query is
---
no user can submit the resume more than once with the same email id ...and if the emailid exists then count the number of occurences of that email id with the userid.
i hope now i have explained my problem well...
do help...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-04 : 23:57:59

CREATE PROC usp_sample
@userid INT,
@EmailID varchar(100),
@return int OUTPUT
AS
IF EXISTS (SELECT 1 FROM YourTable WHERE userid=@userid and emailid = @emailid)
BEGIN
SELECT @return=COUNT(*)
FROM YourTable
WHERE userid=@userid and emailid = @emailid
END
ELSE
PRINT 'No entry'
SET @return=0
END

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-05 : 00:05:06

No user can submit the resume more than once with the same email id

DECLARE @cnt INT

SELECT @cnt = COUNT(1) FROM urtable WHERE emailid = @emailid
INSERT INTO urtable
SELECT ,,,,,,,,,,,,
WHERE @cnt = 0

SELECT @return=COUNT(*)
FROM YourTable
WHERE emailid = @emailid and @cnt <> 0

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-05 : 01:39:47

CREATE PROC sp_test9
@email varchar(100)
As
declare @cnt int
select @cnt= count(*) from test9 where email=@email
BEGIN
if @cnt<>0
BEGIN
print 'Already Have An Account With This Email'
end
else
print 'Proceed with ue Sql Insert Statement'

END
GO

May Be LIke This..

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-01-05 : 03:02:37

thanks a lot...
my problem is solved..:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources