How many parameter in stored procedure

Enter the email address associated with your account. We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic. Anonymous August 28, 0 Comments. Hi All, can any one tell the answer for the below question?

Variables can be user-defined or system variables such as spid. They illustrate how to pass parameters as constants and variables and also how to use a variable to pass the value of a function. When creating a procedure and declaring a parameter name, the parameter name must begin with a single character and must be unique in the scope of the procedure. Explicitly naming the parameters and assigning the appropriate values to each parameter in a procedure call allows the parameters to be supplied in any order.

The data type of a parameter determines the type and range of values that are accepted for the parameter when the procedure is called. For example, if you define a parameter with a tinyint data type, only numeric values ranging from 0 to are accepted when passed into that parameter.

An error is returned if a procedure is executed with a value incompatible with the data type. A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call. If the default value is a character string that contains embedded blanks or punctuation, or if it starts with a number for example, 6xxx , it must be enclosed in single, straight quotation marks.

If no value can be specified appropriately as a default for the parameter, specify NULL as the default. It is a good idea to have the procedure return a customized message if the procedure is executed without a value for the parameter.

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 procedure is executed without a value for the SalesPerson parameter. The following example executes the procedure.

The first statement executes the procedure without specifying an input value. For context Most columns are relatively small with a mix of int and strings varchar What are the issues; good or bad; to having a procedure with a large number of parameters and what is the threshold where I should start considering other patterns?

Joe Celko is an advocate of long parameter lists, which he writes about in detail in this two-part article:. The simplest answer is to use a long parameter list to construct lists and derived tables inside the procedure body. SQL server can handle up to parameters, which should be more than enough for practical purposes. It also is contained in one statement for the optimizer to use. Do I think these techniques will always be the best solution? Of course not.

There is no such thing in SQL. But it is worth looking at when you get an appropriate problem. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. How many stored procedure parameters is too many?

Ask Question. Asked 9 years, 9 months ago.