quote:

---

Originally posted by afrika

hello,
how do i return an output status to a web page from an SP ?

Bascially, i am trying to create a signup page that uses a SP for inserts and later updates ( when choosing a username and password)

I would need to check to see if;
1. The username exists before inserting
2. The phone number is unique

Else, alert the user and ask him to choose another.

This is where i need to pass an output parameter, or an error message with a return value to my web page

Any advice ???

Thanks
Afrika

---

CREATE PROCEDURE AccountInsProc 
(
@userName    varchar(150),
@phoneNumber varchar(50),
@RetStatus   int  OUTPUT
-- Returns 0 for Successful insert
-- Returns 1 for Duplicate userName
-- Returns 2 for Duplicate phoneNumber
-- Returns 3 for Duplicate userName & phoneNumber
)
AS
DECLARE @userNameStat    INT
DECLARE @phoneNumberStat INT

SET @userNameStat = 0
SET @phoneNumberStat = 0

--- First Check the UserName
--  Create a UDF that checks the username is exist; returns 
    0 if not duplicate and returns 
    1 if duplicate
IF (select dbo.CheckUserName(@userName)=1) SET @userNameStat = 1

--- Second Check the PhoneNumber
--  Create a UDF that checks the PhoneNumber is exist; returns 
    0 if not duplicate and returns 
    1 if duplicate
IF (select dbo.CheckPhoneNumber(@phoneNumber)=1) SET    @phoneNumberStat = 1

--FINALLY INSERT IF BOTH CONDITIONS HAVE 0 Value means no Duplicate
IF ((@userNameStat=0) AND (@phoneNumberStat=0))
 BEGIN
   INSERT INTO YourTableName
   VALUES (Value1,Value2)
   -- set the return value to 0, meaning successful
   SET @RetStatus = 0
 END
ELSE
 BEGIN
   -- Test the Status of username and phonenumber
   SET @RetStatus = ( CASE
                      --both duplicates
                      WHEN (@phoneNumberStat = 1 AND @userNameStat=1)
                         THEN 3
                      WHEN (@phoneNumberStat = 1)
                         THEN 2
                      ELSE 1
                      END                      
                    )
 END
 RETURN
 
 --2 sample UDF 
 CREATE FUNCTION CheckUserName 
 (
 @userName varchar(150) 
 )
 RETURNS INT AS
 BEGIN
    DECLARE @RetStat INT
    DECLARE @Result  INT
   
    SET @Result = (Select Count(UserName) from Account where  
                   username = @userName)

    -- already exists
    IF @Result > 0 SET @RetStat = 1
    ELSE SET @RetStat = 0


    RETURN @RetStat
       
 END