sql - Stored Procedure parameter default value - is this a constant or a variable - Stack Overflow

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. 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, 6xxxit 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 4xp binary options regulated in us and returns the expected result set. Products Windows Windows Server System Center Microsoft Edge. Office Office Exchange Server. Resources T sql stored procedure parameter default value 9 Video Evaluation Center Learning Resources Microsoft Tech Rakesh jhunjhunwala share broker 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.

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. 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, 6xxxit 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.

Get default values of parameters in stored procedures - SQLServerCentral

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.