returning diffrent type of values from same sp

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

General SQL Server Forums

New to SQL Server Programming

returning diffrent type of values from same sp

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-27 : 07:21:12

Hi all,

I have a table named 'users' with emailaddress, activation key,userid etc. I want that if user enters his emailaddress, I pass this email address to a stored procedure and check if emailaddress exists and isenable = 0( a bit field to check if user has already set enabled field = 1) then return all user info, else return 0. how to do this in stored procedure.

Regards,
Asif Hameed

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 07:44:05

CREATE PROC Sp_USER
@EMAILID VARCHAR(50)
AS
BEGIN

IF EXISTS(SELECT 'X' FROM TABLE_NAME WHERE EMAILID = @EMAILID AND ISENABLE = 0)
BEGIN
SELECT * FROM USER_TABLE
END
ELSE
BEGIN
SELECT '0'
END

END

-------------------------
R..

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-27 : 07:50:01

how to fetch different types from one sp means a table and an integer value ?

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 07:52:48

hi

sorry am not getting your query.. can you explain pls.

-------------------------
R..

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-27 : 07:59:32

ALTER PROC [dbo].[GetUserDataByEmail]
(
@EmailAddress VARCHAR(100)
)
AS

if exists(SELECT * FROM USERS WHERE EMAIL = @EmailAddress and IsEnable = 0 )
BEGIN
SELECT * FROM USERS WHERE EMAIL = @EmailAddress
RETURN 1
END
else if exists(SELECT * FROM USERS WHERE EMAIL = @EmailAddress and IsEnable = 1 )
BEGIN
RETURN 2 -- your are already active member
END
else
BEGIN
RETURN 0 -- you are not registered please create account
ENd

if first case is true then a row is returned, if second or third case is true then only a single integer value is returned. means different types of result sets are returned.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 08:02:12

hi

OP is fine.. its working right...what is your query on this..

-------------------------
R..

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-27 : 08:19:01

pasted above

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-27 : 08:58:31

suggestions please ?