Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
625 views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...