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, Mar 08 '17 at 12:56

score 1 · Accepted Answer · answered Mar 08 '17 at 12:54

1

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

answered Mar 08 '17 at 12:54

Ed Harper

21,127
4
54
80

Thanks, clearly over engineered, it's what happens when you do inputs without outputs all day! – indofraiser Mar 08 '17 at 12:56
I could not at the time, you have to wait x time (I forget). Done now. :-) – indofraiser Mar 08 '17 at 15:15

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

1 Answers1