时间戳

首页 > 解决方案 > 过程或函数“InsertUpdatePerson”需要未提供的参数“@pr_name”

sql - 过程或函数“InsertUpdatePerson”需要未提供的参数“@pr_name”

问题描述

我收到来自数据库的错误“过程或函数'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'myemail@domain.com',
    @ERR_MESSAGE=@p12 output,
    @ERR_NUMBER=@p13 output

    select @p12, @p13

标签： sqlsql-servertsqlsql-server-2008stored-procedures

解决方案

因此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'myemail@domain.com',
@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'myemail@domain.com',
@ERR_MESSAGE=@p12 output,
@ERR_NUMBER=@p13 output;

如您所见，在这种情况下没有任何好处的开销很大。

推荐阅读