You can create a stored procedure with optional parameters by specifying a default value for optional parameters. When the stored procedure is executed, the default value is used if no other value has been specified.

SP Date parameter default to GETDATE()?

Specifying default values is necessary because a system error is returned if a parameter does not have a default value specified in the stored procedure and the calling program does not provide a value for the parameter when the stored procedure is executed. If no value can be specified appropriately as a default for the parameter, you can specify NULL as the default for a parameter and have the stored procedure return a customized message if the stored procedure is executed without a value for the parameter.

If the default value is a character string that contains embedded blanks or punctuation, or if it begins with a number for example, 6xxx , it must be enclosed in single, straight quotation marks.

NULL is assigned as the default value for the parameter and is used in error handling statements to return a custom error message for cases when the stored procedure is executed without a value for the SalesPerson parameter.

The following example executes the stored procedure. The first statement executes the stored procedure without specifying an input value.

This causes the error handling statements in the stored procedure to return the custom error message.

The second statement supplies an input value and returns the expected result set. Products Windows Windows Server System Center Microsoft Edge. Office Office Exchange Server. Resources Channel 9 Video Evaluation Center Learning Resources Microsoft Tech Companion App Microsoft Technical Communities Microsoft Virtual Academy Script Center Server and Tools Blogs TechNet Blogs.

TechNet Flash Newsletter TechNet Gallery TechNet Library TechNet Magazine TechNet Wiki Windows Sysinternals Virtual Labs. Solutions Networking Cloud and Datacenter Security Virtualization. Updates Service Packs Security Bulletins Windows Update.

sql server - SQL function as default parameter value? - Stack Overflow

Related Sites Microsoft Download Center TechNet Evaluation Center Drivers Windows Sysinternals TechNet Gallery. Training Expert-led, virtual classes Training Catalog Class Locator Microsoft Virtual Academy Free Windows Server courses Free Windows 8 courses SQL Server training Microsoft Official Courses On-Demand. Certifications Certification overview MCSA: Windows 10 Windows Server Certification MCSE Private Cloud Certification MCSE SQL Server Certification MCSE.

Other resources Microsoft Events Second shot for certification Born To Learn blog Find technical communities in your area. Support options For business For developers For IT professionals For technical support Support offerings.

Not an IT pro? Microsoft Customer Support Microsoft Community Forums.

United States English Sign in. Home Library Wiki Learn Gallery Downloads Support Forums Blogs. The content you requested has been removed. Implementing Stored Procedures Creating Stored Procedures Database Engine Specifying Parameters.

Specifying Parameters Specifying a Parameter Default Value. Specifying a Parameter Default Value. Specifying a Parameter Name. Specifying a Parameter Data Type.

SQL Server FAQ - DEFAULT - Providing Default Values to Function Parameters

Specifying the Direction of a Parameter. Collapse the table of content. This documentation is archived and is not being maintained. Specifying a Parameter Default Value You can create a stored procedure with optional parameters by specifying a default value for optional parameters.

Note If the default value is a character string that contains embedded blanks or punctuation, or if it begins with a number for example, 6xxx , it must be enclosed in single, straight quotation marks.

IF SalesPerson IS NULL BEGIN PRINT 'ERROR: You must specify the last name of the sales person. SELECT SalesYTD FROM Sales. SalesPerson AS sp JOIN HumanResources. Is this page helpful? We appreciate your feedback. Site Feedback Site Feedback. Tell us about your experience Flash Newsletter Contact Us Privacy Statement Terms of Use Trademarks.