存储过程参数类型验证

Question

执行存储过程的网络代码

Dim cmd As New SqlCommand("get_list_of_users", connection)
Dim table As New DataTable
Dim adapter As New SqlDataAdapter
Dim param As New SqlParameter("@TypeID", SqlDbType.Int)
param.Direction = ParameterDirection.Input
param.Value = typeID
cmd.Parameters.Add(param)
cmd.CommandType = CommandType.StoredProcedure
adapter.SelectCommand = cmd
adapter.Fill(table)

不幸的是，存储过程中的@TypeID参数没有配置为整数，而是配置了一个位，您可以想象其后果以及我们不得不浪费在调试它上的时间。

有没有办法配置"更严格"模式以将参数传递给存储过程，以便在定义的数据类型与存储过程定义不匹配时引发异常？

谢谢大家

Answer 1

我并不是说这很好，但是，如果你真的想检查，你可以在存储的进程中进行。 下面是一个快速示例，可以为您提供一些可以根据需要实现的内容。在 C# 中，将 int 转换为字符串，并将其作为参数传入，然后将其转换回来，以确保两者匹配。在下面，我像您一样添加了 BIT，因此引发了错误。

CREATE PROCEDURE dbo.pTypeTest
@int BIT            ,
@str NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @bitTheSame     BIT             ,
@intBaseType    NVARCHAR(20)    ,   
@strBaseType    NVARCHAR(20);
SELECT @intBaseType = CAST(SQL_VARIANT_PROPERTY(@int,'BaseType') AS NVARCHAR(20));
SELECT @strBaseType = CAST(SQL_VARIANT_PROPERTY(@str,'BaseType') AS NVARCHAR(20));
SELECT @bitTheSame = CASE WHEN CAST(@int AS NVARCHAR(10)) <> @str THEN 0 ELSE 1 END;
IF(@bitTheSame = 0)
BEGIN
DECLARE @message NVARCHAR(200);
SELECT @message = 'Something went wrong, @str: ' + @str + ' does not match @int: '+CAST(@int AS NVARCHAR(10))+' when it has been converted. @str is a '+@strBaseType+' and @int is a '+@intBaseType;
RAISERROR(@message,16,1);
END
IF(@bitTheSame = 1)
BEGIN
PRINT 'Ok';
END
END

例：

EXEC dbo.pTypeTest @int = 1000,@str = '1000';

引发的错误：

Msg 50000，级别 16，状态 1，过程 pTypeTest，第 24 行 某物 出错了，@str：1000 不匹配 @int：1 时 转换。@str是nvarchar，@int有点

Answer 2

调用具有隐式转换为参数类型的参数值的过程是完全合法的。

但是您可以(并且应该(放弃手写存储过程调用。 而是基于 SQL Server 元数据生成代码，例如，使用 T4 模板。

例如，为您提供过程和参数类型的查询如下所示：

select object_name(m.object_id) ObjectName, 
SCHEMA_NAME(o.schema_id) SchemaName,
o.type_desc ObjectTypeDesc,
p.name ParameterName,
t.name TypeName,
p.precision,
p.scale,
p.max_length,
p.default_value,
p.is_output     
from 
sys.sql_modules m
join sys.objects o
on o.object_id = m.object_id
join sys.parameters p
on m.object_id = p.object_id
join sys.types t
on p.system_type_id = t.system_type_id
order by o.object_id, p.parameter_id