Stored procedure in SQL Server which takes table-valued parameter and insert which does not exists and returns which exists

Question

I am writing a stored procedure which is going to take a table valued parameter as input parameter and will insert if all the row in provided parameter is unique and does not exists in the table User and one which already exists will be returned as output as Duplicate

Table User

    ╔════╦═════════════════════════╦══════════════════╦═══════════╦════════════╦══════════╦════════╦════════════╦═══════════════╦════════════╦════════════╦══════════════╗
    ║ id ║        DateField        ║  registrationno  ║ firstname ║ middlename ║ lastname ║ gender ║    dob     ║ maritalstatus ║  mobileno  ║ landlineno ║   emailid    ║
    ╠════╬═════════════════════════╬══════════════════╬═══════════╬════════════╬══════════╬════════╬════════════╬═══════════════╬════════════╬════════════╬══════════════╣
    ║  1 ║ 2015-10-16 16:24:27.460 ║ U/10/2015/000001 ║ John      ║ NULL       ║ Doe      ║      1 ║ 1990-10-09 ║             1 ║ 1234567890 ║ 1234567890 ║ [email protected] ║
    ║  2 ║ 2015-10-16 16:24:27.460 ║ U/10/2015/000002 ║ Bean      ║ NULL       ║ Bean     ║      1 ║ 1986-18-12 ║             1 ║ 9874561230 ║ 9874561230 ║ [email protected] ║
    ║  3 ║ 2015-10-16 16:24:27.460 ║ U/10/2015/000003 ║ Jennifer  ║ NULL       ║ Lopez    ║      2 ║ 1980-09-16 ║             1 ║ 9985632147 ║ 9985632147 ║ [email protected] ║
    ║  4 ║ 2015-10-16 16:24:27.460 ║ U/10/2015/000004 ║ Maria     ║ NULL       ║ Stewart  ║      2 ║ 2000-10-15 ║             1 ║ 7458612390 ║ 7458612390 ║ [email protected] ║
    ╚════╩═════════════════════════╩══════════════════╩═══════════╩════════════╩══════════╩════════╩════════════╩═══════════════╩════════════╩════════════╩══════════════╝

Table Type UserTableType

CREATE TYPE [dbo].[UserTableType] AS TABLE
(
    [FirstName] [nvarchar](max) NULL,
    [MiddleName] [nvarchar](max) NULL,
    [Lastname] [nvarchar](max) NULL,
    [Gender] [nvarchar](max) NULL,
    [DateOfBirth] [date] NULL,
    [MaritalStatus] [nvarchar](max) NULL,
    [MobileNo] [nvarchar](max) NULL,
    [LandlineNo] [nvarchar](max) NULL,
    [EmailId] [nvarchar](max) NULL
)
GO

The procedure as far I had written:

Create PROCEDURE [dbo].[uspManageUser] 
        @UserTableType dbo.User READONLY
AS
BEGIN

SET NOCOUNT ON;

    BEGIN TRY
        DECLARE @countI INT , @countU INT, @countT INT

        SELECT @countI = count(temp.MobileNo) 
        FROM 
            (SELECT 
                    VPM.FirstName, VPM.MiddleName, VPM.LastName, 
                    CASE VPM.Gender 
                    WHEN 'Male' THEN '1' 
                    WHEN 'Female' THEN 'G2' 
                    END AS [Gender], 
                    getdate() as 'DOB', 
                    CASE VPM.MaritalStatus 
                    WHEN 'Single' THEN 1 
                    END as 'MaritalStatus', 
                    VPM.MobileNo, VPM.LandlineNo, 
                    VPM.EmailId, VPM.Occupation, 
                    VPM.Address1, VPM.Address2, VPM.City, VPM.Pincode
                FROM 
                    @UserTableType AS VPM
                LEFT JOIN 
                    User AS DPM ON DPM.MobileNo = VPM.MobileNo AND VPM.EmailId = DPM.EmailId) temp

        SET @countT= @countI    
        Select @countT

        INSERT INTO 
            dbo.[User](DateField, FirstName,  MiddleName,  LastName,  Gender, dob,   MaritalStatus,  MobileNo, LandlineNo,  EmailId)
        SELECT 
            Getdate(), 
            VPM.FirstName, 
            VPM.MiddleName, 
            VPM.LastName, 
        CASE 
            VPM.Gender  when 'Male'   then 'GE001' 
                        when 'Female' then 'GE002' END,
            getdate(),
        CASE 
            VPM.MaritalStatus when 'Single' then 1 end,
            VPM.MobileNo,
            VPM.LandlineNo,
            VPM.EmailId,
            VPM.Occupation,
            VPM.Address1,
            VPM.Address2,
            VPM.City,
            VPM.Pincode
        FROM
            @UserTableType AS VPM
        LEFT JOIN 
            [User] AS DPM ON DPM.MobileNo = VPM.MobileNo AND VPM.EmailId = DPM.EmailId

        RETURN 1

    END TRY

BEGIN CATCH 
    RETURN -1
END CATCH
END

What I want is that a stored procedure in which I read the data from UserTableType parameter and if there is a duplicate which i will check on basis of User.MobileNo and User.EmailId because they are the unique field in the User table and if no record exists against the given User.MobileNo and User.EmailId it will be inserted else if record exists for User.MobileNo and User.EmailId then a return output will be the resultset which already exists in User table

How to go ahead in this?

So what is your actual problem, in addition to just writing the code? — James Z
my problem is i am not getting it worked kindly review the code and help if possible. — haxor
You do understand that Stack Overflow is for questions, not debugging problems with your code? — James Z

James Z James Z · Accepted Answer · 2015-10-17T07:14:05

Just a guess what you need, but here is some (pseudo)code that you might be after.

Return rows that exists already:

select * from @UserTableType VPM
where exists (
  select 1 from User DPM
  where DPM.MobileNo = VPM.MobileNo AND VPM.EmailId = DPM.EmailId
)

Insert rows that don't exist:

insert into User ("list of columns")
select "list of columns" from @UserTableType VPM
where not exists (select 1 from User DPM
  where DPM.MobileNo = VPM.MobileNo AND VPM.EmailId = DPM.EmailId
)

Stored procedure in SQL Server which takes table-valued parameter and insert which does not exists and returns which exists

1 Answers