USE [SRO_R_Accountdb]
GO
/****** Object:  StoredProcedure [dbo].[_CertifyUser]    Script Date: 01/03/2015 13:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[_CertifyUser]
    @szUserID       VARCHAR(25),
    @szPassword VARCHAR(50),
    @szSSNumber VARCHAR(32)
AS
    DECLARE @nUserJID INT
    DECLARE @sec_primary tinyint
    DECLARE @sec_content tinyint
    SET @nUserJID       = 0
    SET @sec_primary    = 0
    SET @sec_content    = 0
    IF (NOT EXISTS(SELECT JID FROM TB_User WHERE StrUserID = @szUserID))
    BEGIN
        SELECT CONVERT( INT, 0), CONVERT( tinyint, 0), CONVERT( tinyint, 0)
        RETURN
    END
    SELECT @nUserJID = JID FROM TB_User WHERE StrUserID = @szUserID
    IF (@nUserJID = 0 OR @nUserJID IS NULL OR @@error <> 0 OR @@rowcount = 0)
    BEGIN
        SELECT CONVERT( INT, 0), CONVERT( tinyint, 0), CONVERT( tinyint, 0)
        RETURN
    END
    IF (NOT EXISTS(SELECT JID FROM TB_User WHERE JID = @nUserJID AND password = @szPassword))
    BEGIN
        SELECT CONVERT( INT, 0), CONVERT( tinyint, 0), CONVERT( tinyint, 0)
        RETURN
    END

    SELECT @sec_primary = sec_primary, @sec_content = sec_content FROM dbo.TB_User WITH (nolock) WHERE JID = @nUserJID
    SELECT @nUserJID, @sec_primary, @sec_content