CODEWITHSUNDEEP
sql-servervb.netstored-procedures
ab_732
ab_732

Reputation: 3897

Stored procedure Parameter Type validation

net code that executes a stored procedure 

        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)

Unfortunately the @TypeID parameter in the stored procedure was not configured to be an integer but a bit, you can imagine the consequences and the amount of time we had to waste debugging it.

Is there a way to configure a "stricter" mode to pass parameters to a stored procedure so that an exception would be raised if the data type defined does not match the stored proc definition?

Thanks all

Upvotes: 0

Views: 2444

Answers (2)

Keith
Keith

Reputation: 1038

I am not saying this is good, but, if you really want to check, you could do it in the stored proc. Below is a quick example that may give you a few things you could implement if you wanted. In C# convert the int to string, and pass it in as a parameter also, then it is converted back, to make sure the two match. In the below, I have added the BIT as you had, so the error is raised. 

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

Example:

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

Raised error:

Msg 50000, Level 16, State 1, Procedure pTypeTest, Line 24 Something went wrong, @str: 1000 does not match @int: 1 when it has been converted. @str is a nvarchar and @int is a bit

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

It's perfectly legal to call a procedure with parameter values that have implicit conversions to the parameter types.

But you can (and should) move away from hand writing the stored procedure calls. Instead generate the code based on the SQL Server metadata, using, for instance, a T4 template.

EG a query to give you the procedures and parameter types would look like this:

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

Upvotes: 0

Related Questions