如何在SQL Server中验证存储过程参数及是否有通用校验函数

一、参数类型不匹配的验证处理 #

SQL Server会在存储过程执行前先做参数类型校验，若传入参数与定义类型不匹配且无法隐式转换，会直接抛出系统错误，不会进入存储过程内部执行。要在SQL Server端实现自定义的参数有效性验证并返回明确的无效参数提示，需要调整存储过程的参数定义方式：

实现步骤 #

1. 将目标参数（如int/bigint类型）先定义为varchar兼容类型，允许接收字符串输入；
2. 在存储过程内部使用TRY_CONVERT或TRY_CAST函数判断输入是否能转换为目标类型；
3. 转换失败时抛出自定义错误信息并终止执行。

示例代码 #

ALTER PROCEDURE Sp_test
    (@Para1 varchar(50), 
     @Para2 varchar(50),
     @Para3 varchar(50))
AS
BEGIN
    SET NOCOUNT ON;

    -- 验证@Para1是否为有效int
    IF TRY_CONVERT(int, @Para1) IS NULL
    BEGIN
        RAISERROR('参数@Para1不是有效的整数类型', 16, 1);
        RETURN;
    END

    -- 验证@Para2是否为有效bigint
    IF TRY_CONVERT(bigint, @Para2) IS NULL
    BEGIN
        RAISERROR('参数@Para2不是有效的大整数类型', 16, 1);
        RETURN;
    END

    -- 转换为目标类型后执行业务逻辑
    DECLARE @ValidPara1 int = CONVERT(int, @Para1);
    DECLARE @ValidPara2 bigint = CONVERT(bigint, @Para2);

    -- 业务逻辑示例
    SELECT @ValidPara1 AS Para1, @ValidPara2 AS Para2, @Para3 AS Para3;
END

二、通用参数校验函数的实现 #

可以自定义一个通用的表值函数，支持对不同数据类型的参数进行校验和转换，避免在每个存储过程中重复编写校验逻辑。

通用校验函数代码 #

CREATE FUNCTION dbo.ValidateAndConvertValue
(
    @InputValue varchar(MAX),
    @TargetType varchar(50)
)
RETURNS @Result TABLE
(
    IsValid bit,
    ConvertedValue sql_variant,
    ErrorMessage varchar(200)
)
AS
BEGIN
    DECLARE @IsValid bit = 1;
    DECLARE @ConvertedValue sql_variant;
    DECLARE @ErrorMessage varchar(200) = '';

    BEGIN TRY
        -- 根据目标类型执行转换
        SET @ConvertedValue = CASE @TargetType
            WHEN 'int' THEN CONVERT(int, @InputValue)
            WHEN 'bigint' THEN CONVERT(bigint, @InputValue)
            WHEN 'varchar' THEN CONVERT(varchar(MAX), @InputValue)
            WHEN 'datetime' THEN CONVERT(datetime, @InputValue)
            WHEN 'decimal' THEN CONVERT(decimal(18,2), @InputValue)
            -- 可根据需求扩展更多数据类型
            ELSE NULL
        END;

        -- 针对非varchar类型，若转换后为NULL则标记为无效
        IF @ConvertedValue IS NULL AND @TargetType <> 'varchar'
        BEGIN
            SET @IsValid = 0;
            SET @ErrorMessage = '无法转换为指定类型: ' + @TargetType;
        END
    END TRY
    BEGIN CATCH
        -- 捕获转换异常，标记为无效并返回错误信息
        SET @IsValid = 0;
        SET @ErrorMessage = '转换失败: ' + ERROR_MESSAGE();
    END CATCH

    INSERT INTO @Result (IsValid, ConvertedValue, ErrorMessage)
    VALUES (@IsValid, @ConvertedValue, @ErrorMessage);

    RETURN;
END

在存储过程中调用通用函数 #

ALTER PROCEDURE Sp_test
    (@Para1 varchar(50), 
     @Para2 varchar(50),
     @Para3 varchar(50))
AS
BEGIN
    SET NOCOUNT ON;

    -- 校验@Para1
    DECLARE @Para1Valid bit, @Para1Value sql_variant, @Para1Err varchar(200);
    SELECT @Para1Valid = IsValid, @Para1Value = ConvertedValue, @Para1Err = ErrorMessage
    FROM dbo.ValidateAndConvertValue(@Para1, 'int');

    IF @Para1Valid = 0
    BEGIN
        RAISERROR('参数@Para1无效: %s', 16, 1, @Para1Err);
        RETURN;
    END

    -- 校验@Para2
    DECLARE @Para2Valid bit, @Para2Value sql_variant, @Para2Err varchar(200);
    SELECT @Para2Valid = IsValid, @Para2Value = ConvertedValue, @Para2Err = ErrorMessage
    FROM dbo.ValidateAndConvertValue(@Para2, 'bigint');

    IF @Para2Valid = 0
    BEGIN
        RAISERROR('参数@Para2无效: %s', 16, 1, @Para2Err);
        RETURN;
    END

    -- 转换为具体类型后执行业务逻辑
    DECLARE @ValidPara1 int = CONVERT(int, @Para1Value);
    DECLARE @ValidPara2 bigint = CONVERT(bigint, @Para2Value);

    SELECT @ValidPara1 AS Para1, @ValidPara2 AS Para2, @Para3 AS Para3;
END

内容的提问来源于stack exchange，提问作者Dev