How to fix the error procedure expects parameter &

问题:

I'm trying to use a stored procedure to display the results of a table, and an associated table or recent changes to the database. The stored procedure is:

set ANSI_NULLS ON
set NOCOUNT ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[MKTG_Current]( @added smalldatetime OUTPUT, @named varchar(50) OUTPUT)
AS

DECLARE @pDate smalldatetime;
DECLARE @AIID int;
DECLARE @Table varchar(50);
DECLARE @Bork nvarchar(350);
SET @pDate = GETDATE()


SELECT @Table=[Table], @AIID=AIID, @added=date_added  FROM MKTG_Recent WHERE date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, @pDate))
SET @named = @Table
SET @Bork = 'SELECT * FROM ' + QUOTENAME(@Table) + ' WHERE AIID= ' + cast(@AIID as varchar(100))

EXECUTE sp_executesql @Bork, @added OUTPUT, @named OUTPUT


SELECT @added, @named 

It's supposed to return to items in addition to the results from the select statement. There are no inputs to the stored procedure. The stored procedure compiles fine in SQL Management Studio (2008), but the page returns an error: Microsoft OLE DB Provider for SQL Server error '80040e14'

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. index.asp, line 61

Line 61 of the page is in bold:

  dim Objrs, cmd
  set Objrs = Server.CreateObject("ADODB.RecordSet")
  set cmd = Server.CreateObject("ADODB.Command")
  set conn = Server.CreateObject("ADODB.Connection")
  conn.Open strConnect
  set cmd.ActiveConnection = conn
  cmd.CommandText="MKTG_Current"
  cmd.CommandType=adCmdStoredProc
  cmd.Parameters.Append cmd.CreateParameter("@added", 135, 2)
  cmd.Parameters.Append cmd.CreateParameter("@named", 200, 2, 50)
Line 61 **set Objrs = cmd.Execute**
  name_of_table = cmd.Parameters("@named")
  added = cmd.Parameters("@added")  

I'm of the impression that this is caused by a SQL code error, but I'm not seeing it. A quick check of the Objrs.state is returning a 0, which means the problem definitely lies in the SQL code. For the life of me, I can't identify why this error is being generated.

回答1:

In this answer, I will try to recreate the issue that you mentioned in the question and will also explain how I resolved this.

First, let's create two tables named dbo.MKTG_Recent and dbo.Table_1 using the scripts under Create Tables Script section. I created these tables based on some assumptions that I made using the data provided in the question. Using the script, the table dbo.MKTG_Recent will be populated with 1 record.

Next, create the stored procedure named dbo.MKTG_Current using the script provided under Create Stored Procedure Script section.

If, we try to execute the stored procedure with EXEC command as EXEC MKTG_Current null, null, the error message Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. will be thrown. Refer screenshot #1

After reading through MSDN about the usage of the procedure sp_executesql, I found that the second parameter of the stored procedure defines the types of the output parameters and it has to be a Unicode string. So, I modified the stored procedure by providing the second parameter as Unicode string by prefixing with N. Refer screenshot #2 for the stored procedure change.

Screenshot #3 shows the output of the stored procedure dbo.MKTG_Current after making the changes. The stored procedure will produce two outputs. One for the query statement in the variable @Bork that is being passed to sp_executesql and the other output corresponds to the SELECT statement that displays the OUTPUT variables.

Based on the requirement, I am not sure if you even need to call sp_executesql, you can write the stored procedure as shown under Simplified Stored Procedure Section. I could be wrong because I don't fully understand the requirement. Screenshot #4 shows output of the simplified stored procedure. SELECT statement is not required because the values are being passed through the OUTPUT parameters. I have included the SELECT statement only to show the query output.

Hope this points you in the right direction.

Create Tables Script:

CREATE TABLE [dbo].[MKTG_Recent](
    [Table] [varchar](40) NOT NULL,
    [AIID] [int] NOT NULL,
    [date_added] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table_1](
    [AIID] [int] NOT NULL,
    [added] [smalldatetime] NOT NULL,
    [named] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.MKTG_Recent ([Table], AIID, date_added) 
VALUES ('Table_1', 1, '2011-08-01')
GO

Create Stored Procedure Script:

SET ANSI_NULLS ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MKTG_Current]
(       @added  SMALLDATETIME   OUTPUT
    ,   @named  VARCHAR(50)     OUTPUT
)
AS

DECLARE @pDate  SMALLDATETIME;
DECLARE @AIID   INT;
DECLARE @Table  VARCHAR(50);
DECLARE @Bork   NVARCHAR(350);

SET     @pDate  = GETDATE()

SELECT  @Table  = [Table]
    ,   @AIID   = AIID
    ,   @added  = date_added  
FROM    dbo.MKTG_Recent 
WHERE   date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, @pDate))

SET @named  = @Table
SET @Bork   = ' SELECT  * 
                FROM    ' + QUOTENAME(@Table) + ' 
                WHERE   AIID= ' + CAST(@AIID AS VARCHAR(100))

EXECUTE sp_executesql   @Bork
                    ,   @added OUTPUT
                    ,   @named OUTPUT

SELECT  @added
    ,   @named 
GO    

Simplified Stored Procedure:

SET ANSI_NULLS ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MKTG_Current]
(       @added  SMALLDATETIME   OUTPUT
    ,   @named  VARCHAR(50)     OUTPUT
)
AS

DECLARE @Table  VARCHAR(50);

SELECT  @named  = [Table]
    ,   @added  = date_added  
FROM    dbo.MKTG_Recent 
WHERE   date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, GETDATE()))

SELECT  @added AS added
    ,   @named AS named 
GO

Screenshots:

#1: Execution showing the error message

#2: Change made to the stored procedure

#3: Stored procedure output after the changes

#4: Simplified stored procedure output

回答2:

You declared @Bork as NVARCHAR. So why do you say:

SET @Bork = 'SELECT ...';

? Should be:

SET @Bork = N'SELECT ...';

This is how we define NVARCHAR (Unicode) strings. The N stands for national. If you leave out that N prefix, sp_executesql assumes it is VARCHAR, and that leads to the error.

EDIT for Kieren

While technically yes, you CAN declare NVARCHAR literals without the N prefix, there are several reasons why you should never do so. One is to avoid the error that user873479 is receiving. Others are to ensure correct results. Some examples:

(A) Let's try sp_executesql with the N prefix and without. Even though there are no actual Unicode characters in the string, forgetting to put the N prefix when calling sp_executesql leads to the exact same error this question is about:

EXEC sp_executesql N'SELECT 1';
EXEC sp_executesql 'SELECT 1';

Results

====
1
====
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

(B) Now let's try a very simple assignment of a Unicode character to an NVARCHAR variable. Notice how, without the N prefix, the actual value gets lost?

DECLARE @x NVARCHAR(32) = 'Ǝ';
SELECT @x;
SET @x = N'Ǝ';
SELECT @x;

Results

====
?
====
Ǝ

(C) Now let's take it a step further. Let's put some Unicode data into a table:

DECLARE @foo TABLE(bar NVARCHAR(1));
INSERT @foo(bar) SELECT N'Ǝ';

-- now someone comes along looking for the row, without using N:
SELECT COUNT(*) FROM @foo WHERE bar = 'Ǝ';

Results

====
0

And I could come up with more examples where implicitly switching between CHAR/VARCHAR and NCHAR/NVARCHAR can turn seeks into scans, but I think error messages and incorrect results should be enough for now.

So sure, you can get away with declaring NVARCHAR literals without using the N prefix, but only if you're not calling procedures that expect NVARCHAR, and only if your data doesn't actually include any Unicode characters (in which case I would have to wonder why you bothered to use NVARCHAR in the first place).