What are all the different types of parameters that can be passed to SQL Server - StoreProcedures ?

Answer:

There are three types of parameters can be passed to SQL Server Stored procedures. They are
Input parameter
Output parameter
InputOutput parameter.

CREATE PROC SPTYPEOFPARAMS 

(

	@PARAMINPUT VARCHAR(20) = 'Input Param', 

	@PARAMOUTPUT VARCHAR(50) OUTPUT,

	@PARAMINOUT VARCHAR(50) OUTPUT

)

AS

BEGIN

	SET @PARAMOUTPUT = @PARAMINPUT + ' Assigned to Output'

	SET @PARAMINOUT = @PARAMINOUT + ' Assigned to Input Output'

END

Note that there is no keyword for INPUTOUTPUT parameter. The default parameter is INPUT. Here we have three parameters namely @PARAMINPUT, @PARAMOUTPUT, @PARAMINOUT. @PARAMINPUT is used to pass the value to SP ,@PARAMOUTPUT is used to get the result value from SP and @PARAMINOUT is used to pass the value to SP and get the value from SP. If we execute the SP as follows :

Declare @OutVar1 as Varchar(50)

Declare @OutVar2 as Varchar(50)

SET @OutVar2 = 'Before SP'

Exec SPTYPEOFPARAMS 'Example',@OutVar1 OUTPUT,@OutVar2 OUTPUT

SELECT 'Output' =@OutVar1,'InputOutput' = @OutVar2

we get the following result :

Output	                                     InputOutput

Example Assigned to Output	Before SP Assigned to Input Output