Implicit conversions of ms SQL Server Stored Procedure Parameters

The columns and stored procedure parameters of Microsoft SQL Server 2000 data tables are of the type, but I found that many parameter types can be "mixed" when writing stored procedures, for example, the following stored procedure:

/**/ /*

Name: stp_testdifferentparatype_int

Function: test the impact of different parameter types on stored procedures.

Author: waxdoll Cheung

Date: 2005-04-07

*/


Create   Procedure DBO. stp_testdifferentparatype_int

-- Year and month

@ Tongjinianyue Int

As

  Select   [ FM _ product registration ] . *   From   [ FM _ product registration ]   Where (Year = @ Tongjinianyue)


  Return

Go

And the following stored procedure:

/**/ /*

Name: stp_testdifferentparatype_char

Function: test the impact of different parameter types on stored procedures.

Author: waxdoll Cheung

Date: 2005-04-07

*/


Create   Procedure DBO. stp_testdifferentparatype_char

-- Year and month

@ Tongjinianyue Char ( 4 )

As

  Select   [ FM _ product registration ] . *   From   [ FM _ product registration ]   Where (Year = @ Tongjinianyue)


  Return

Go

The output results are the same when testing the stored procedure, and the data column is of the type -- char (4 ),

Why? The stored procedure performs implicit type conversion on parameters during execution.

Note the following when determining the parameter type when writing a stored procedure:

1. Try to use the same data type as the data column to reduce the implicit type conversion;

2. Not all types can be implicitly converted. If different data types are used, an error may occur: 


Figure: data type conversion of Microsoft SQL Server

In addition, SQL Server user-defined function parameters are similar.