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
Asked In: Many Interviews |
Alert Moderator