Error Converting Data Type Int to Nvarchar

Error: Error converting data type int to nvarchar

This error occurs when a stored procedure which returns a value is used.

e.g

CREATE PROCEDURE [dbo].[spr_SP1]
@ColA INT,
@AutoID INT OUTPUT
AS
BEGIN
 INSERT INTO TableA ( ColA )
 VALUES ( @ColA )

 SELECT @AutoID = SCOPE_IDENTITY();
END

When adding the parameter to the command on the code, usually the code used is:

command.Parameters.AddWithValue("@AutoID", System.DBNull.Value)
command.Parameters("@AutoID").Size = 4
command.Parameters("@AutoID").Direction = ParameterDirection.Output

However, this portion of code would possibly cause error during deployment. To avoid the error, use the code below instead:

Dim param = New System.Data.SqlClient.SqlParameter()
param.ParameterName = "@AutoID"
param.Direction = ParameterDirection.Output
param.Size = 8
command.Parameters.Add(param)

Read More