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

 Posted by Nagasundar_Tn on 11/29/2012 | Category: Sql Server Interview questions | Views: 2993 | Points: 40
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 

Comments or Responses

Login to post response