I'm writing some stored procs in SQL Server 2008, and wondered if the concept of optional input parameters is possible here?
I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored proc, then take things from there, but I was interested if the concept is available here. Thanks!
FMFF
1,6304 gold badges32 silver badges59 bronze badges
asked Nov 27, 2009 at 21:09
1
You can declare like this
CREATE PROCEDURE MyProcName @Parameter1 INT = 1, @Parameter2 VARCHAR [100] = 'StringValue', @Parameter3 VARCHAR [100] = NULL AS /* check for the NULL / default value [indicating nothing was passed */ if [@Parameter3 IS NULL] BEGIN /* whatever code you desire for a missing parameter*/ INSERT INTO ........ END /* and use it in the query as so*/ SELECT * FROM Table WHERE Column = @Parameteranswered Nov 27, 2009 at 21:19
Raj MoreRaj More
46k31 gold badges128 silver badges193 bronze badges
2
Yes, it is. Declare parameter as so:
@Sort varchar[50] = NULLNow you don't even have to pass the parameter in. It will default to NULL [or whatever you choose to default to].
answered Nov 27, 2009 at 21:11
Mike ColeMike Cole
13.9k25 gold badges110 silver badges189 bronze badges
5
2014 and above at least you can set a default and it will take that and NOT error when you do not pass that parameter. Partial Example: the 3rd parameter is added as optional. exec of the actual procedure with only the first two parameters worked fine
exec getlist 47,1,0 create procedure getlist @convId int, @SortOrder int, @contestantsOnly bit = 0 asanswered Nov 15, 2018 at 18:22
billpennockbillpennock
4211 gold badge6 silver badges14 bronze badges
1
The default mentioned above only works for simple cases. In more complicated cases, I use an IF clause near the beginning of the proc to provide a value, if the parameter is NULL or empty and calculations are required.
I often use optional parms in the WHERE clause, and discovered that SQL does not short circuit logic, so use a CASE statement to make sure not to try to evaluate NULL or empty dates or unique identifiers, like so:
CREATE Procedure ActivityReport [ @FromDate varchar[50] = NULL, @ToDate varchar[50] = NULL ] AS SET ARITHABORT ON IF @ToDate IS NULL OR @ToDate = '' BEGIN SET @ToDate = CONVERT[varchar, GETDATE[], 101] END SELECT ActivityDate, Details FROM Activity WHERE 1 = CASE WHEN @FromDate IS NULL THEN 1 WHEN @FromDate = '' THEN 1 WHEN ActivityDate >= @FromDate AND ActivityDate < DATEADD[DD,1,@ToDate] THEN 1 ELSE 0 ENDanswered Jun 14 at 19:50
Bruce PatinBruce Patin
3057 silver badges10 bronze badges
Not the answer you're looking for? Browse other questions tagged sql-server-2008 stored-procedures optional-parameters or ask your own question.
Here you will learn about stored procedure parameters, optional parameters, and executing stored procedures with parameters in SQL Server.
- A stored procedure can have zero or more INPUT and OUTPUT parameters.
- A stored procedure can have a maximum of 2100 parameters specified.
- Each parameter is assigned a name, a data type, and direction like Input, Output, or Return. If a direction is not specified, then by default, it is Input.
- You can specify a default value for the parameters.
- Stored procedures can return a value to the calling program if the parameter is specified as OUTPUT.
- The parameter values must be a constant or a variable. It cannot be a function name.
- Parameter variables can be either user-defined or system variables like @spid
Stored Procedure with Input Parameters
Consider the following stored procedure example with the input parameters.
CREATE PROCEDURE uspUpdateEmpSalary [ @empId int ,@salary money ] AS BEGIN UPDATE dbo.Employee SET Salary = @salary WHERE EmployeeID = @empId END
In the above stored procedure uspUpdateEmpSalary, the @empId and @Salary are INPUT parameters. By default, all the parameters are INPUT parameters in any stored procedure unless suffix with OUTPUT keyword. @empId is of int type and @salary is of money data type. You pass the INPUT parameters while executing a stored procedure, as shown below.
EXEC dbo.uspUpdateEmpSalary @EmpId = 4, @Salary = 25000 -- or EXEC dbo.uspUpdateEmpSalary 4, 25000
Parameter Names
- The stored procedure parameters names must start with a single @.
- The name must be unique in the scope of the stored procedure.
- If parameter values are passed as @Param1 = value1, @ Param2 = value2 as shown in the above example, then the parameters can be passed in any order.
- If one parameter is supplied as @param1 = value, then all parameters must be supplied in the same manner.
OUTPUT Parameters
The OUTPUT parameter is used when you want to return some value from the stored procedure. The calling program must also use the OUTPUT keyword while executing the procedure.
The following stored procedure contains INPUT and OUTPUT parameters.
CREATE PROCEDURE uspGetManagerID @empId int, @managerId int OUTPUT AS BEGIN SELECT @managerId = ManagerID FROM dbo.Employee WHERE EmployeeID = @empId END
In the above uspGetManagerID stored procedure, @manageId is an OUTPUT parameter. The value will be assigned in the stored procedure and returned to the calling statement. The following pass the OUTPUT parameter while executing the stored procedure.
DECLARE @managerID int EXECUTE uspGetManagerID @empId = 2, @managerId OUTPUT PRINT @managerId
Above, the uspGetManagerID is called by passing INPUT parameter @employeeID = 2 and @managerID OUTPUT as the output parameter. Notice that we have not assigned any value to an OUTPUT variable @managerID and also specified the OUTPUT keyword.
There are a total of three methods of returning data from a stored procedure: OUTPUT parameter, result sets, and return codes.
Result sets: If the body of the stored procedure has a SELECT statement, then the rows returned by the select statement are directly returned to the client.
Return code: A stored procedure can return an integer value called the Return code which will indicate the execution status of the procedure. You specify the return code using the RETURN keyword in the procedure.
Optional Parameters
SQL Server allows you to specify the default values for parameters. It allows you to skip the parameters that have default values when calling a stored procedure.
The default value is used when no value is passed to the parameter or when the DEFAULT keyword is specified as the value in the procedure call.
Specify the default value when you declare parameters, as shown below.
CREATE PROCEDURE uspUpdateEmpSalary [ @empId int ,@salary money = 1000 ] AS BEGIN UPDATE dbo.Employee SET Salary = @salary WHERE EmployeeID = @empId END
Above, @empsalary money = 0 declares @salary parameter and assigns the default value. Now, you can call the above procedure without passing @salary parameter, as shown below.
EXEC uspUpdateEmpSalary 4
The above statement will update the Salary column with the default value 1000 for the EmployeeID 4. Thus, making @salary parameter as optional.
Want to check how much you know SQL Server?