String or binary data would be truncated when using it in a stored procedure

Question

I create this TYPE table in database:

CREATE TYPE [dbo].[AccessLevel] AS TABLE
(
      [RoleId] [INT] NULL,
      [Access] [NVARCHAR](1) NULL,
      [IsDelete] [BIT] NULL
)
GO

and I need to insert data into it but when I use this code:

DECLARE @AccessLevel AccessLevel 

INSERT INTO @AccessLevel VALUES (4, 'dfdfg', 0)
INSERT INTO @AccessLevel VALUES (4, 'dfdfg', 0)

EXEC InsertAndUpdateAccessLevel @AccessLevel

and this is my stored procedure:

ALTER PROCEDURE [dbo].[InsertAndUpdateAccessLevel]
    (@AccessLevel AS AccessLevel READONLY)
AS
BEGIN
    INSERT INTO RoleAccess (RoleId, Access, IsDelete)
        SELECT * FROM @AccessLevel
END

I get this error:

Msg 8152, Level 16, State 4, Line 5
String or binary data would be truncated

What's the problem? How can I solve this problem?

You're trying to INSERT the value 'dfdfg' in an [nvarchar](1). That is clearly more than 1 character. — Larnu

DineshDB DineshDB · Accepted Answer · 2019-11-29T09:26:35

You are trying to insert 5 length character('dfdfg') into your 1 length column [Access] [nvarchar](1).

Increase the value of your column in Table type.

CREATE TYPE [dbo].[AccessLevel] AS TABLE(
  [RoleId] [int] NULL,
  [Access] [nvarchar](25) NULL,
  [IsDelete] [bit] NULL
 )
GO

String or binary data would be truncated when using it in a stored procedure

1 Answers