Understanding Parameters in SQL Procedures and Functions
1- Definition of Parameters:
Parameters are variables that allow you to pass values into stored procedures or functions. They can be of various data types, such as integers, strings, dates, etc.
Each parameter must be defined with a specific data type and can be either input (to pass data into the procedure/function) or output (to return data from the procedure/function).
2- Creating a Procedure or Function:
When you create a stored procedure or function, you define the parameters it accepts. Here’s an example of a simple stored procedure:
Create proc Get_Student
(
@Name Varchar(100),
@Gender Varchar(20)='male',
@studentId int=Null
)
As
Begin
Declare @cond Nvarchar(Max)='',@query Nvarchar(Max)=''
IF @Name<>'' Set @cond+=' And studentName='''+@Name+''''
IF @studentId<>0 Set @cond+=' And StudentId='''+Cast(@studentId as varchar)+''''
IF @Gender<>'' Set @cond+=' And Gender='''+@Gender+''''
Set @query='Select * from StudentDetails where 1=1' +@cond
Execute (@query)
End
In this example, the procedure Get_Student expects parameter @Name, of type Varchar (@gender parameter has default value , and @studentId parameter is nullable).
Calling a Procedure or Function:
When you call a stored procedure or function, you must provide the required parameters. If you fail to do so, you will encounter the error message in question. Here’s how to correctly call the above procedure:
If you omit the parameter or provide an incorrect type, you will receive an error similar to:
Default Values and Make Nullable:
If a parameter has a default value or nullable defined, it is optional. However, if you do not provide a value for a non-default or non-nullable parameter, the error will occur.