ADO.NET Parameter Direction

Input InputOutput Output ReturnValue Passing value to procedure   or function. Pass value to the procedure or function and get back the assigned value from the procedure or function. Get value from the procedure or function. Get the value of the return statement. ParameterDirection .Input; ParameterDirection .InputOutput ParameterDirection .Output ParameterDirection .ReturnValue (@Columndatetime   datetime)    By default it is IN. So   it is not possible to use the keyword IN. (@Columndatetime datetime OUT)    Since OUT implies both IN and OUT and there is no INOUT. (@Columndatetime datetime OUT) We are not passing the parameter in Procedure or function.           DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC @ReturnValue = [dbo]. [GetTestTable] @Columnnumeric, 'BABU' PRINT @ReturnValue We can assign the value any where in the procedure or function.          We can assign value at declaration itself.          (@Columndatetime datetime = 10 OUT)    If we didn’t assign value in the procedure or function it took the sent value.    If we didn’t pass value and we didn’t assing value in the procedure or function but we assign default value it took the default value. We can assign the value any where in the procedure or function.          We can assign value at declaration itself.          (@Columndatetime datetime = 10 OUT)       If we didn’t assign value in the procedure or function it took the sent value.    If we didn’t pass value and we didn’t assing value in the procedure or function but we assign default value it took the default value.       DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC [dbo].[GetTestTable] @Columnnumeric OUT, 'BABU' PRINT @Columnnumeric This should be the last statement of the procedure or function.          return 10         or like this.    DECLARE @ReturnValue numeric SET @ReturnValue = 10 RETURN 10       Get value from the procedure or function.    DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC @ReturnValue = [dbo].[GetTestTable] @Columnnumeric PRINT @ReturnValue                                         objCmd. Parameters .Add(" @Columnnumeric" , SqlDbType .Decimal) .Value = 10.0d; objCmd.Parameters .Add("@ Columnnumeric", SqlDbType. Decimal) .Value = 10.0d; objCmd. Parameters["@ Columnnumeric"] .Direction = Parameter Direction . InputOutput; objCmd. Parameters .Add("@ Columnnumeric", SqlDbType. Decimal). Value = 10.0d; objCmd. Parameters["@ Columnnumeric"] .Direction = Parameter Direction . Output; objCmd. Parameters .Add(" @Columnnumeric", SqlDbType .Decimal). Value = 10.0d; objCmd. Parameters [" @Columnnumeric"] .Direction = Parameter Direction .ReturnValue;           Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value; Decimal Columnnumeric   = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value; Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value;                               If the ParameterDirection is ParameterDirection .ReturnValue we should not use the parameter in prarameter declaration of the procedure or function. !supportEmptyParas]> <![endif]> http://weblogs.asp.net/andrewrea/archive/2008/02/19/examples-of-using-system-data-parameterdirection-with-sql-server.aspx Rules to follow Rule 1 If our ParameterDirection is ParameterDirection.ReturnValue and we are using in the parameter of the Procedure or Function it expects the parameter. And we throw this error. Procedure 'GetTestTable' expects parameter '@Columnnumeric', which was not supplied. We should not use the parameter in the procedure if we set ParameterDirection as ParameterDirection.ReturnValue in ADO.net. E.g.   CREATE PROCEDURE [dbo].[GetTestTable] (@Columndatetime DATETIME,                                        @Columnnumeric  NUMERIC) AS BEGIN       RETURN 15 END objCmd.Parameters.Add("@Columndatetime", SqlDbType.DateTime).Value = objPropertiesClassName.Columndatetime; objCmd.Parameters.Add("@Columnnumeric", SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric; objCmd.Parameters["@Columnnumeric"].Direction = ParameterDirection.ReturnValue; objCmd.ExecuteNonQuery(); int i = (int)objCmd.Parameters["@Columnnumeric"].Value; Rule 2 Consider that we have given the parameter order as given below. The parameter @ColumnnTest will contain the return value. We will think that we have specified the direction ReturnValue to @Columnnumeric before @ColumnnTest and @Columnnumeric will contain the value. But it is not like that. Since we added the parameter @ColumnnTest before @Columnnumeric. It took according to the order of parameter that we are adding to the command. objCmd.Parameters.Add("@ColumnnTest", SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric; objCmd.Parameters.Add("@Columnnumeric", SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric; objCmd.Parameters["@Columnnumeric"].Direction = ParameterDirection.ReturnValue; objCmd.Parameters["@ColumnnTest"].Direction = ParameterDirection.ReturnValue; int j = (int)objCmd.Parameters["@ColumnnTest"].Value; object i = (object)objCmd.Parameters["@Columnnumeric"].Value; Rule 3 objCmd.Parameters.Add("@ColumnnTest", SqlDbType.Decimal).Value = 20.0d; objCmd.Parameters.Add("@Columnnumeric", SqlDbType.Decimal).Value = 10.0d; objCmd.Parameters["@Columnnumeric"].Direction = ParameterDirection.ReturnValue; objCmd.Parameters["@ColumnnTest"].Direction = ParameterDirection.ReturnValue;   If the procedure or function doesn’t return any value, the first added parameter tooks the default value.   But the next parameter took the value that we asigned.   Here @ColumnnTest returns 0.0d, while ("@Columnnumeric returns the assigned value 10.0d.   We can ask instead of ReturnValue can we use ExecuteScalar(), since it also returns single value. The ExecuteScalar returns a single value that is in the form of a result set.  That means the value must be "SELECTed" in the SQL. I.e. if the result-set contains 3 columns and 2 rows, it took only the 1st row and 0th column value. The remaining are discarded.  Using the RETURN keyword is possible through the use of a ReturnValue parameter. References Configuring Parameters and Parameter Data Types (ADO.NET) Examples of using System.Data.ParameterDirection with Sql Server