NEWID() SQL Server using procedures

Question

I use NORTHWIND database and I have to use procedure which inserts new value into dbo.Customers.

IF OBJECT_ID('dbo.usp_InsertCustomer') IS NOT NULL
    DROP PROC dbo.usp_InsertCustomer;
GO
CREATE PROC dbo.usp_InsertCustomer
    @CompanyName AS nvarchar(40), 
    @ContactName AS nvarchar(30) = NULL,
    @ContactTitle AS nvarchar(30) = NULL,
    @Address AS nvarchar(60) = NULL,
    @City AS nvarchar(15) = NULL,
    @Region AS nvarchar(15) = NULL,
    @PostalCode AS nvarchar(10) = NULL,
    @Country AS nvarchar(15) = NULL,
    @Phone AS nvarchar(24) = NULL,
    @Fax AS nvarchar(24) = NULL
AS
BEGIN
    INSERT INTO dbo.Customers(CustomerID, CompanyName, ContactName, ContactTitle,
        Address, City, Region, PostalCode, Country, Phone, Fax)
    VALUES (NEWID(), @CompanyName, @ContactName, @ContactTitle, @Address, @City,
         @Region, @PostalCode, @Country, @Phone, @Fax);
END
GO

And when I tried to use this procedure

EXEC dbo.usp_InsertCustomer @CompanyName = N'MyCompany'

I get

Arithmetic overflow error converting expression to data type nvarchar.

Answer 1

Assuming that you have the Northwind DB that is around the internet, CustomerID is expect a value of nchar(5), and NEWID() will return a uniqueidentifier. Try changing this, so you can insert the correct value into CustomerID.

EDIT:

This is a VERY BAD way to generate an automatic ID, but for your situation, you can do something like this:

(SELECT MAX(CONVERT(INT, ISNULL(CustomerID, '0'))) + 1 FROM dbo.Customers)

This is assuming that all the CustomerIDs values are integer on the table.

The best way to go is what Sean Lange mentioned on the comments: add the IDENTITY property to the CustomerID column (for this, you need to change the data type to INT), and let SQL SERVER take care of the auto increment.

Answer 2

DECLARE @CustomerID uniqueidentifier
SET @CustomerID = NEWID()
SELECT SUBSTRING(CAST(@CustomerID AS NVARCHAR(36)),0,5)

This helped.