CODEWITHSUNDEEP
sql-servert-sql
Lalit Rajput
Lalit Rajput

Reputation: 301

Update table using stored procedure in SQL Server 2014

I have two tables, please see attached schema, and I have written a stored procedure which is not working. Please help me.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[countries]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Prefix] [nvarchar](max) NULL,
    [MinDigits] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[customers]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Business] [nvarchar](max) NULL,
    [Phone] [nvarchar](max) NULL,
    [CountryID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample data in the tables:

Id  Name      Business   Phone            CountryID
---------------------------------------------------
 1  John      B&B LLC    001820199202        ***
 2  Mike      ABC Inc    006192479121        ***
 3  Jennifer  Coca Cola  0017421             ***
 4  Sabine    ABC Inc    0091827411          ***
...

Countries:

Id   Name          Prefix   MinDigits
--------------------------------------
 1   USA           001        10
 2   Australia     0061       11
 3   India         0091        9
...

I want to update the CountryID in the customers table based on the countries table.

Result based on above:

Id  Name      Business   Phone            CountryID
---------------------------------------------------
 1  John      B&B LLC    001820199202        1
 2  Mike      ABC Inc    006192479121        2
 3  Jennifer  Coca Cola  0017421             ***
 4  Sabine    ABC Inc    0091827411          3
...

The entry for Jennifer is not updated, since minimum digits are not matching

This is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[UpdateCustomer]
AS
BEGIN
    SELECT *
    INTO #CustomerTable 
    FROM dbo.customers

    DECLARE @Phone NVARCHAR
    DECLARE @CountryID INT
    DECLARE @Count INT
    DECLARE @CustomerID INT

    WHILE EXISTS (SELECT * FROM #CustomerTable)
    BEGIN
        SELECT TOP 1 
            @Phone = Phone, @CustomerID = Id
        FROM
            #CustomerTable

        IF((SELECT TOP 1 COUNT(*) 
            FROM countries 
            WHERE @Phone LIKE Prefix+'%' And LEN(@Phone) <= MinDigits) > 0)
        BEGIN
            SELECT TOP 1 
                @CountryID = Id 
            FROM
                countries 
            WHERE
                @Phone LIKE Prefix + '%' 
                AND LEN(@Phone) <= MinDigits

            UPDATE customers 
            SET CountryID = @CountryID 
            WHERE Id = @CustomerID
        END

        DELETE #CustomerTable
        WHERE Id = @CustomerID
    END

    DROP TABLE #CustomerTable
END

Upvotes: 1

Views: 187

Answers (5)

Lalit Rajput
Lalit Rajput

Reputation: 301

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[UpdateCustomer] AS BEGIN SELECT * INTO #CustomerTable FROM dbo.customers

DECLARE @Phone NVARCHAR(MAX)
DECLARE @CountryID INT
DECLARE @Count INT
DECLARE @CustomerID INT

WHILE EXISTS (SELECT * FROM #CustomerTable)
BEGIN
    SELECT TOP 1 
        @Phone = Phone, @CustomerID = Id
    FROM
        #CustomerTable

    IF((SELECT TOP 1 COUNT(*) 
        FROM countries 
        WHERE @Phone LIKE Prefix+'%' And LEN(@Phone) >= MinDigits) > 0)
    BEGIN
        SELECT TOP 1 
            @CountryID = Id 
        FROM
            countries 
        WHERE
            @Phone LIKE Prefix + '%' 
            AND LEN(@Phone) >= MinDigits

        UPDATE customers 
        SET CountryID = @CountryID 
        WHERE Id = @CustomerID
    END

    DELETE #CustomerTable
    WHERE Id = @CustomerID
END

DROP TABLE #CustomerTable

END

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

Still, If you want to do it from your SP then define size of @phone variable as 

DECLARE @Phone NVARCHAR(30) -- as needed

Its not holding assigned phone number so the update statement is not working.

Upvotes: 1

neer
neer

Reputation: 4092

Use UPDATE FROM

UPDATE customers
SET CountryID = C.Id   
FROM
     Countries C
WHERE
    LEFT(customers.Phone, LEN(C.Prefix)) = C.Prefix  AND
    LEN(customers.phone) >= C.MinDigits

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

First of all SQL is set based, so using loop concept is not best idea.

You could use MERGE statement:

MERGE customers c
USING countries cs
  ON c.Phone LIKE CONCAT(cs.Prefix, '%')
 AND LEN(c.Phone) >= cs.MinDigits
WHEN MATCHED THEN
  UPDATE 
  SET CountryId = cs.Id;

LiveDemo

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

You could just do something like this as your entire stored procedure

UPDATE  cu
SET     cu.CountryId = co.Id
FROM    customers cu
JOIN    countries co ON cu.phone LIKE co.prefix + '%'
WHERE   LEN(cu.phone) >= co.MinDigits

Upvotes: 2

Related Questions