SQL 如何修复错误过程需要“ntext/nchar/nvarchar”类型的参数“@parameters”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6904451/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix the error procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'?
提问by Techmaniac
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'
除了来自 select 语句的结果之外，它还应该返回到项目。存储过程没有输入。存储过程在 SQL Management Studio (2008) 中编译良好，但页面返回错误：Microsoft OLE DB Provider for SQL Server error '80040e14'
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. index.asp, line 61
过程需要类型为“ntext/nchar/nvarchar”的参数“@parameters”。index.asp，第 61 行
Line 61 of the page is in bold:
页面的第 61 行以粗体显示：
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.
我的印象是这是由 SQL 代码错误引起的，但我没有看到。快速检查 Objrs.state 返回 0，这意味着问题肯定出在 SQL 代码中。对于我的一生，我无法确定为什么会产生此错误。
回答by
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_Recentand dbo.Table_1using the scripts under Create Tables Scriptsection. 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_Recentwill be populated with 1 record.
首先，让我们创建一个名为两个表dbo.MKTG_Recent，并dbo.Table_1在使用这些脚本创建表脚本部分。我根据使用问题中提供的数据所做的一些假设创建了这些表。使用该脚本，该表dbo.MKTG_Recent将填充 1 条记录。
Next, create the stored procedure named dbo.MKTG_Currentusing the script provided under Create Stored Procedure Scriptsection.
接下来，创建dbo.MKTG_Current使用创建存储过程脚本部分下提供的脚本命名的存储过程。
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
如果，我们尝试使用 EXEC 命令执行存储过程，则会抛出EXEC MKTG_Current null, null错误消息Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.。参考截图#1
After reading through MSDNabout 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 #2for the stored procedure change.
在阅读MSDN关于过程sp_executesql的用法后，我发现存储过程的第二个参数定义了输出参数的类型，它必须是一个Unicode字符串。因此，我通过以 N 为前缀将第二个参数作为 Unicode 字符串提供来修改存储过程。有关存储过程更改，请参阅屏幕截图#2。
Screenshot #3shows the output of the stored procedure dbo.MKTG_Currentafter 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.
屏幕截图#3显示了dbo.MKTG_Current进行更改后存储过程的输出。存储过程将产生两个输出。一个用于传递给 sp_executesql 的变量 @Bork 中的查询语句，另一个输出对应于显示 OUTPUT 变量的 SELECT 语句。
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 ProcedureSection. I could be wrong because I don't fully understand the requirement. Screenshot #4shows output of the simplified stored procedure. SELECTstatement is not required because the values are being passed through the OUTPUTparameters. I have included the SELECTstatement only to show the query output.
根据需求，我不确定您是否甚至需要调用 sp_executesql，您可以编写如Simplified Stored ProcedureSection下所示的存储过程。我可能是错的，因为我不完全理解要求。屏幕截图#4显示了简化存储过程的输出。SELECT语句不是必需的，因为值是通过OUTPUT参数传递的。我包含SELECT语句只是为了显示查询输出。
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
#1：执行显示错误消息
#2:Change made to the stored procedure
#2：对存储过程所做的更改
#3:Stored procedure output after the changes
#3：更改后的存储过程输出
#4:Simplified stored procedure output
#4：简化的存储过程输出
回答by Aaron Bertrand
You declared @Bork as NVARCHAR. So why do you say:
您将 @Bork 声明为NVARCHAR. 那你为什么说：
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_executesqlassumes it is VARCHAR, and that leads to the error.
这就是我们定义NVARCHAR（Unicode）字符串的方式。N代表国家。如果您省略了 N 前缀，则sp_executesql假定它是VARCHAR，这会导致错误。
EDITfor Kieren
为 Kieren编辑
While technically yes, you CANdeclare NVARCHARliterals 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:
虽然在技术上是的，你CAN声明NVARCHAR没有N个前缀文字，有几个原因，你不应该这样做。一是避免user873479收到的错误。其他的则是为了确保正确的结果。一些例子：
(A) Let's try sp_executesqlwith 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_executesqlleads to the exact same error this question is about:
(A) 让我们尝试sp_executesql使用和不使用 N 前缀。即使字符串中没有实际的 Unicode 字符，在调用时忘记放置 N 前缀sp_executesql也会导致与此问题完全相同的错误：
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 NVARCHARvariable. Notice how, without the N prefix, the actual value gets lost?
(B) 现在让我们尝试将 Unicode 字符非常简单地分配给NVARCHAR变量。请注意，如果没有 N 前缀，实际值是如何丢失的？
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:
(C) 现在让我们更进一步。让我们将一些 Unicode 数据放入一个表中：
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.
我可以想出更多的例子，其中隐式切换 CHAR/VARCHAR 和 NCHAR/NVARCHAR 可以将搜索转换为扫描，但我认为错误消息和不正确的结果现在应该足够了。
So sure, you can get away with declaring NVARCHARliterals 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 NVARCHARin the first place).
可以肯定的是，您可以在NVARCHAR不使用 N 前缀的情况下声明文字，但前提是您没有调用预期的过程NVARCHAR，并且仅当您的数据实际上不包含任何 Unicode 字符时（在这种情况下，我不得不怀疑为什么你一开始就费心使用NVARCHAR）。
