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

mr-dortaj

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?

DineshDB

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How should i throw exception error from stored procedure such as " String or binary data would be truncated. "?

error, string or binary data would be truncated when trying to insert

SQL Server String or binary data would be truncated

EF Core - String or binary data would be truncated

String or binary data would be truncated WHERE IN

Msg 8152 String or binary data would be truncated

Python: DataError - String or Binary data would be truncated

String or binary data would be truncated in Varbinary(MAX)

"String or binary data would be truncated" on DBCC CHECKIDENT

string or binary data would be truncated. when large string is inserted in a field

How could I get "String or binary data would be truncated" when the value being updated is not too long?

Azure Data Factory Error - String or binary data would be truncated

System.Data.SqlClient.SqlException: String or binary data would be truncated

'String or binary data would be truncated' without any data exceeding the length

Enterprise Architect Source Import warns that string or binary data would be truncated

DbUpdateException: Which field is causing "String or binary data would be truncated"

Error 'String or binary data would be truncated' in Microsoft SQL

String or binary data would be truncated: Error not caught by TRY_CAST

String or binary data would be truncated. ASP VB.NET

String Or Binary Data Would Be Truncated - Column is Big Enough

How to fix error "String or binary data would be truncated" in SQL Server

"String or binary data would be truncated." for NVARCHAR but not VARCHAR in LIKE operation

How to fix this error: Conversion failed when converting the nvarchar value 'String or binary data would be truncated.' to data type int

String or binary data would be truncated.The statement has been terminated while removing data

"String or binary data would be truncated" upon insert despite the data seemingly fitting

String or binary data would be truncated. The statement has been terminated at System.Data.SqlClient.SqlConnection

How to display specified table column for Error Number: 22001/8152 SQL Server String or binary data would be truncated?

how to find "String or binary data would be truncated" error on sql in a big query

Comparing text to varchar(max) parameter gives String or binary data would be truncated