Stored procedures--problems with the default values of parameters

--Today, you encounter a problem, stored procedures default value problem, and then check MSDN, so remember
--http://technet.microsoft.com/zh-cn/library/ms189330.aspx

--look at the execution of several processes first

--First create our usual writing.

CREATE PROC P (@S VARCHAR (1) = ' A ')

As

SELECT @S

Go

EXEC P

Go
--1
ALTER PROC P (@S decimal=a)
As
SELECT @S
Go
EXEC P
Go
/*

Server: Message 8114, Level 16, State 5, Process P, line 0
An error occurred while converting the data type nvarchar to numeric.

*/

--2
ALTER PROC P (@S VARCHAR (1) = medium)
As
SELECT @S
Go
EXEC P
Go
/*

----

(The number of rows affected is 1 rows)

*/
--4
ALTER PROC P (@S NVARCHAR (1) = medium)
As
SELECT @S
Go
EXEC P
Go
/*
----
In

(The number of rows affected is 1 rows)

*/
--Data type Priority http://msdn.microsoft.com/zh-cn/library/ms190309 (sql.90). aspx
The--1 parameter defaults to pass some character types can be defined without adding ' '.
In the--2 parameter conversion process, the default value overrides the type conversion of the argument, otherwise the highest level in this type

--5
ALTER PROC P (@S VARCHAR) =1AA)
As
SELECT @S
Go
EXEC P
Go
/*
Server: Message 170, Level 15, State 1, Process P, line 1
Line 1th: syntax error near ' AA '.
*/

--6
ALTER PROC P (@S VARCHAR) =a A)
As
SELECT @S
Go
EXEC P
Go
/*
Server: Message 170, Level 15, State 1, Process P, line 1
Line 1th: There are grammatical errors near ' a '.
*/

--This article can refer to the above Web site
--/* the default value of the specified parameter
--You can create a stored procedure with optional parameters by specifying a default value for the optional parameter. When the stored procedure is executed, the default value is used if no other value is specified.
--
-If you do not specify a default value for the parameter in the stored procedure, and the caller does not provide a value for the parameter when the stored procedure is executed, a system error is returned, so specifying a default value is necessary.
--
--If you cannot specify the appropriate default value for a parameter, you can specify NULL as the default value for the parameter and, if the stored procedure is executed without supplying the parameter value, the stored procedure returns a custom message.
--
--Note:
--If the default value is a string that contains embedded spaces or punctuation marks, or begins with a number (for example, 6xxx), the default value must be enclosed in straight single quotes. */

---blog for the first time, there is no technical content, you are welcome to throw brick guidance, the function of the default parameters are similar, we can try

--Original post http://topic.csdn.net/u/20100709/22/4ca8d993-aa1a-4157-b2c3-7227006d8b6b.html?seed=1368739759&r= 66863373#r_66863373

--Since the default value of stored procedure parameters, of course, the function, so extend the

--parameter default values for functions and stored procedures, parameter types, return type what is the difference?
--1 is mainly in the execution of some differences, stored procedures can omit the parameters with default values, while the function is not, you must specify the default keyword to replace
--2 parameter type, return type

--Refer to Books Online

--Stored procedures
/*
--Maximum parameter limit,
The parameters in the procedure. You can declare one or more parameters in the CREATE PROCEDURE statement. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined).
A stored procedure can have up to 2.1 parameters.

Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers.
The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures.

By default, parameters can only be substituted for constants and cannot be used in place of a table name, column name, or other database object name. For more information, see EXECUTE.

--Parameter data type

The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures.
However, the cursor data type can only be used for OUTPUT parameters. If the specified data type is cursor,
You must also specify both varying and OUTPUT keywords. For more information about the data types and their syntax provided by SQL Server, see Data types.

--Output type and default

Varying

Specifies the result set that is supported as output parameters (dynamically constructed by stored procedures and content can be changed). Only cursor parameters are applicable.

Default

The default value for the parameter. If you define a default value, you do not have to specify the value of the parameter to execute the procedure. The default value must be constant or NULL.
If the procedure uses the LIKE keyword for this parameter, the default value can contain wildcard characters (%, _, [], and [^]).

OUTPUT

Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure.
The Text, ntext, and image parameters can be used as OUTPUT parameters. Output parameters that use the input keyword can be cursor placeholders.

N

Represents a placeholder that can specify up to 2.1 parameters.
*/

*/

--function
/*
--The maximum parameter limit, and the default value designation
The parameters of the user-defined function. You can declare one or more parameters in the CREATE FUNCTION statement. A function can have up to 1,024 parameters.
The value of each declared parameter must be specified by the user when the function executes, unless the default value of the parameter is already defined. If the parameter of the function has a default value,
You must specify the "default" keyword to get the default value when calling the function. This behavior differs from the parameter that has a default value in the stored procedure, and omitting the parameter in the stored procedure also means using the default value.

Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers. The arguments for each function are used only for the function itself;
The same parameter names can be used in other functions. Parameters can only be substituted for constants, not for table names, column names, or other database object names.

--Parameter data type

The data type of the parameter. All scalar data types, including bigint and sql_variant, can be used as parameters for user-defined functions.
Timestamp data types and user-defined data types are not supported.
Non-scalar types (such as cursor and table) cannot be specified.

--Return type

is the return value of a scalar user-defined function. Scalar_return_data_type
Can be any of the scalar data types supported by SQL Server (except text, ntext, image, and timestamp).

*/

--Summary

--Parameter type
--Stored procedures can use cursor as parameter types, but output parameters, and there is no maximum limit,
--The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. Maximum Limit 2100
--The data type of the parameter. All scalar data types, including bigint and sql_variant, can be used as parameters for user-defined functions. Maximum limit 1024

--Default value
--Stored procedure defaults, can be executed without specifying the default value of the function to specify default keyword

--Return type
--function
-Can be any of the scalar data types supported by SQL Server (except text, ntext, image, and timestamp).
--Process
--Use the OUTPUT parameter to return information to the calling procedure.
The--text, ntext, and image parameters can be used as OUTPUT parameters. Output parameters that use the input keyword can be cursor placeholders

--A workaround for converting getdate () to a function default value, a creates a getdate view, and B is passed as a parameter; know both of these practices and welcome new approaches .

--The stored procedure can dynamically exec execute the statement, equal to can pass in the table masterpiece parameter, but the function does not support the dynamic execution; ;;;

--refer to Books Online and MSDN, reproduced without author information