过程或函数'InsertUpdatePerson'需要参数"@pr_name"，但未提供

Question

我收到一个来自数据库的错误"过程或函数"InsertUpdatePerson"需要参数"@pr_name"，但未提供该参数"我检查了存储过程，发现没有任何丢失的参数。我已经从C#、SQL Server Profiler和SQL Server执行了操作，并且得到了相同的消息。

您可以在下面找到存储过程：

ALTER PROCEDURE [dbo].[InsertUpdatePerson]
@pr_id INT = NULL,
@pr_name NVARCHAR(50),
@pr_gender bit,
@pr_nationality NVARCHAR(20),
@pr_idn NVARCHAR(12),
@pr_passport nvarchar(10),
@pr_resident bit,
@pr_phone NVARCHAR(11),
@pr_email NVARCHAR(100),
@ERR_MESSAGE NVARCHAR(255) = NULL OUTPUT,
@ERR_NUMBER INT = NULL OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRAN
IF @pr_id = NULL
IF EXISTS(SELECT pr_email FROM Person WHERE pr_email = @pr_email) 
SET @ERR_MESSAGE = 'The email is already exist. Please enter another email.'
ELSE IF EXISTS(SELECT pr_idn FROM Person WHERE pr_idn = @pr_idn) 
SET @ERR_MESSAGE = 'The ID number is already exist. Please enter another ID number.'
ELSE
BEGIN
INSERT INTO Person
(pr_name
,pr_gender
,pr_nationality
,pr_idn
,pr_passport
,pr_resident
,pr_phone
,pr_email)
VALUES
(@pr_name
,@pr_gender
,@pr_nationality
,@pr_idn
,@pr_passport
,@pr_resident
,@pr_phone
,@pr_email)
END
ELSE
IF EXISTS(SELECT pr_email FROM Person WHERE pr_email = @pr_email)
SELECT @ERR_MESSAGE = 'The email is already exist. Please enter another email.'
ELSE IF EXISTS(SELECT pr_idn FROM Person WHERE pr_idn = @pr_idn)
SELECT @ERR_MESSAGE = 'The ID number is already exist. Please enter another ID number.'
ELSE
UPDATE Person
SET pr_name = @pr_name
,pr_gender = @pr_gender
,pr_nationality = @pr_nationality
,pr_idn = @pr_idn
,pr_passport = @pr_passport
,pr_resident = @pr_resident
,pr_phone = @pr_phone
,pr_email = @pr_email
WHERE pr_id = @pr_id
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK 
SET @ERR_MESSAGE = ERROR_MESSAGE()
SET @ERR_NUMBER = ERROR_NUMBER()
END CATCH

END

SQL Server中的执行脚本如下：

declare @p12 nvarchar(255)
set @p12=NULL
declare @p13 int
set @p13=NULL
exec sp_executesql N'InsertUpdatePerson',N'@pr_id nvarchar(4000),
@pr_name nvarchar(4),@pr_gender bit,@pr_nationality nvarchar(6),
@pr_idn nvarchar(12),@pr_passport nvarchar(7),@pr_resident bit,
@pr_phone nvarchar(11),@pr_email nvarchar(20),
@ERR_MESSAGE nvarchar(255) output,
@ERR_NUMBER int output',
@pr_id=NULL,
@pr_name=N'Fadi',
@pr_gender=1,
@pr_nationality=N'Japan',
@pr_idn=N'288120500034',
@pr_passport=N'P000000',
@pr_resident=1,
@pr_phone=N'12345678912',
@pr_email=N'[e-mail]',
@ERR_MESSAGE=@p12 output,
@ERR_NUMBER=@p13 output
select @p12, @p13

Answer 1

因此sp_executesql用于执行动态SQL。由于SQL没有任何动态，您应该直接执行SP，例如

exec dbo.InsertUpdatePerson
@pr_id=NULL,
@pr_name=N'Fadi',
@pr_gender=1,
@pr_nationality=N'Japan',
@pr_idn=N'288120500034',
@pr_passport=N'P000000',
@pr_resident=1,
@pr_phone=N'12345678912',
@pr_email=N'[e-mail]',
@ERR_MESSAGE=@p12 output,
@ERR_NUMBER=@p13 output;

如果你有理由动态执行你的SP，你需要提供执行它所需的完整SQL作为第一个参数，即再次列出所有参数如下：

exec sp_executesql N'exec dbo.InsertUpdatePerson @pr_id,
@pr_name,
@pr_gender,
@pr_nationality,
@pr_idn,
@pr_passport,
@pr_resident,
@pr_phone,
@pr_email,
@ERR_MESSAGE,
@ERR_NUMBER'
,N'@pr_id int,@pr_name nvarchar(50),@pr_gender bit,@pr_nationality nvarchar(20),
@pr_idn nvarchar(12),@pr_passport nvarchar(10),@pr_resident bit,
@pr_phone nvarchar(11),@pr_email nvarchar(100),
@ERR_MESSAGE nvarchar(255) output,
@ERR_NUMBER int output',
@pr_id=NULL,
@pr_name=N'Fadi',
@pr_gender=1,
@pr_nationality=N'Japan',
@pr_idn=N'288120500034',
@pr_passport=N'P000000',
@pr_resident=1,
@pr_phone=N'12345678912',
@pr_email=N'[e-mail]',
@ERR_MESSAGE=@p12 output,
@ERR_NUMBER=@p13 output;

正如你所看到的，在这种情况下，这是大量的开销，没有任何好处。