Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'

Question

Error: Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

Aim: Pass back a value based on Sql

Code: (VB.NET)

With command
    .Connection = connection
    .CommandText = "spAuditLoggerSystemUser" 'include audit names
    .CommandType = CommandType.StoredProcedure
    .Parameters.Clear()
    .Parameters.AddWithValue("@EmployeeID", CInt(vEmployeeID))

    Dim parameter As SqlParameter = command.Parameters.Add("@LineID", SqlDbType.Int)
    parameter.Direction = ParameterDirection.Output
    command.ExecuteNonQuery()
    vLineID = command.Parameters("@LineID").Value.ToString()

    .ExecuteNonQuery()
End With

Stored Procedure:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAuditLoggerSystemUser]
(
    @LineID INT OUTPUT,
    @EmployeeID int  
)

AS

BEGIN

SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX) 


SELECT @SQL = 'SELECT ID  
FROM taylorwoodrow.dbo.TblEmployeeDetails WHERE EmployeeID = ''' + (LTRIM(RTRIM(@EmployeeID))) + '''';

EXEC sp_executesql @SQL,@LineID OUT; 
PRINT @LineID 

END

If do the following I return a (correct) print value:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @EmployeeID AS INT
DECLARE @LineID NVARCHAR(MAX)
SET @EmployeeID = '1213'

SELECT @SQL = 'SELECT ID  
FROM DB.dbo.TblEmployeeDetails WHERE EmployeeID = ''' + (LTRIM(RTRIM(@EmployeeID))) + '''';

EXEC sp_executesql @SQL,@LineID OUT; 
PRINT @LineID

I used Execute stored procedure with an Output parameter? as a reference

In the sp you are declaring LineID as INT and in your workin sample it's nvarchar(max)! Why do you build the select string that way instead of just performing a select? — COeDev

Ed Harper Ed Harper · Accepted Answer · 2017-03-08T12:54:15

There's no need for you to use dynamic SQL in your stored procedure. Try:

ALTER PROCEDURE [dbo].[spAuditLoggerSystemUser]
(
    @LineID INT OUTPUT,
    @EmployeeID int  
)

AS

BEGIN

SET NOCOUNT ON;

SELECT @LineID = ID  
FROM taylorwoodrow.dbo.TblEmployeeDetails 
WHERE EmployeeID = @EmployeeID 

END

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'

1 Answers