SQL SERVER Parameter Directions

There are 4 types of parameter direction in SQL SERVER.

1. Input
2. Output or Out
3. InputOutput
4. Return

But we can specify only OUT or OUTPUT as parameter direction for the Procedure or Function.

RETURN at the last statement of the Procedure or Function and not in the parameter declaration of the Procedure or Function.

IN

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID


ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

We can’t able to specify in any where during the execution or in the SP parameter direction.

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID IN –Not allowed

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT IN) -–Not allowed AS
BEGIN
SELECT @ParentID
END

We will get this error.
Incorrect syntax near the keyword 'in'.

OUTPUT
Output is not only for sending value back to the caller, it also accepts input from the caller. Since by default OUT implies both IN and OUT.

E.g. 1

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID = @ParentID + 5
END


DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID 

Result: 11

There is no need of RETURN or SELECT statement for the OUT direction parameter. Just assing the value, we will get the result.

If we didn’t assing a new value, we will get only the value we sent during execution of the Procedure or Function.(i.e. 6 for the above condion.)

If we didn’t sent any value during execution of the Procedure or Function we will get only nothing for the below condion.

DECLARE @ParentID int
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

E.g. 2

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

No need to pass value. Just specify the keyword OUT near the parameter.

DECLARE @ParentID INT
EXEC [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

Result: 10

For the above Procedure or Function we are assigning value to the parameter and also we are using SELECT statement. Here we will get a result-set and aslo the value assigned to our argument.

RETURN

There should be a RETURN statement at the end of the Procedure or Function.

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
RETURN @ParentID
END

DECLARE @ParentID    INT
DECLARE @ReturnValue INT
SET @ParentID     =6
EXEC @ReturnValue = [dbo].[GetTestTable] @ParentID
PRINT @ReturnValue

If there is no RETURN statemnt at the end of the Procedure or Function, we will get only the default value of the RETURN argument datatype.

For example there is no RETURN statemnt for the above Procedure the result will be 0.

We can’t able to return a table variable from a RETURN statement.


Note:

1. By default OUT implies both IN and OUT.
2. Since our parameter is OUT we can pass value, since OUT implies both IN and OUT.
3. The default parameter direction is IN.
4. If the last statement is just RETURN without any value or a variable we will get
   nothing (if we didn’t pass value to the parameter of assign) or default value (if
   we assign value when passing). This is default for all the parameter direction.

These all are wrong.
@ReturnValue EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

@ReturnValue = EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

EXEC [dbo].[GetTestTable] @ParentID, @ReturnValue RETURN

Procedure or Function GetTestTable has too many arguments specified.

Three tire code Generator for C#.NET

Download the toolOr

1. Select Table Tab to get the tables for your ConnectionString.
2. Enter your connection string.
3. Press to get the tables for your ConnectionString.
4. After pressing the button Load Tables the tables are loaded. When selecting a table it focus on the Conditions tab and all the column and DataType are loaded automatically in 9, 10, 10.1.
5. Conditon tab to specify the property and field prefix.
6. Namespace for your class.
7. Name of your property class.
8. Name of your data access layer class.
9. Enter your table name. This helps to produce the sp name. For Select method it creates spname as GetTableName.
10. Table column name along with DataType.
11. 10.1. Get distinct of DataType from 10.
12. Clear all the controls.
13. For each datatype in 10.1 we are generating a textbox to enter the prefix for the DataType.
14. For each data-type you want to specify a separate prefix check it. For exampele for string specify str. Your field will be created like strUserID. The panel will be enabled and enter it.
15. By default the field names are generated with underscore (_). If you want to change give a new one.
16. Enter the prefix for the fields. This will be enabled by checking Is prefix required for fields.
17. For properties some of us specify pUserID. If you want to specify enter it by check the checkbox of is prefix required for properties.
18. To genereate the Data Tire Classes.
19. If your get procedure contains any parameter check the select.
20. If your Delete procedure contains any parameter check the select.
21. Select the column names. These are the parameters for procedure Insert and Update.
22. This is single select. This column will be your direction. For all the four procedures.
23. Select your ParameterDirection
24. Select your ParameterDirection.
25. Select your ParameterDirection.
26. Select your ParameterDirection.
27. If you are using SQLHelper class check it. The code generated accordingly.
28. If you want to save the calsses as file check it. It opens a folder dialogue to select a path where to store the class files.
29. After select you can view the selected path.
30. The tab Properties contains a class with properties.
31. The tab DAL contains a class with functions for calling Select, Insert, Delete Update procecures.
32. The tab SQLHelper contains a class with functions for calling Select, Insert, Delete Update procecures. This class uses SQLHelper.cs

Happy Coding.

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

Errors and Solution

Error 1:

string isEmpty = string.Empty;
DateTime dt = isEmpty == string.Empty ? BNull.Value : DateTime.Now;

Error
Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'System.DateTime'

Solution
Cast it to object.

string isEmpty = string.Empty;
DateTime dt = Convert.ToDateTime(isEmpty == string.Empty ? (object)DBNull.Value : (object)DateTime.Now);

User Defined Functions

Types of User Defined Functions
I. Scalar Functions
II. In-Line Table Functions
III. Multistatement Table Functions

Accept parameters of any scalar data type except rowversion

Scalar Functions	In-Line Table Functions	Multistatement Table Functions
Returns only scalar value.	Return a table variable that was created by a single select statement.	Returns a table variable. No multi result set. This type of function is used when we want to apply more logic except we can express in a single query. I.e. like stored procedure we can manipulate with more statements.
Support multiple T-SQL statements.	Supports only sing T-SQL Statement.	Support multiple T-SQL statements.
Return type is fixed.	No need to specify the table column names and its type.	We have to specify the table columns.
CREATE FUNCTION Testscalar(@ID INT) RETURNS INT AS BEGIN SELECT @ID = 10 SELECT @ID = @ID + 1 SELECT @ID = @ID + 1 RETURN @ID END	CREATE FUNCTION [dbo].[Testinline] ( Params ) RETURNS TABLE AS RETURN SELECT * FROM dbo.treeview CREATE FUNCTION [dbo].[Testinline](@i INT) RETURNS TABLE AS RETURN SELECT @i AS [ColName]	CREATE FUNCTION [dbo].[Testmultiline] ( @ID INT ) RETURNS @TableVariable TABLE ( I INT, J VARCHAR(10)) AS BEGIN INSERT INTO @TableVariable SELECT 1, '10' UNION ALL SELECT 2, '20' UNION ALL SELECT 3, '30' RETURN END
The last statement included within a function must be a return statement RETURN statements in scalar valued functions must include an argument. Simply retrun statemet is wrong.	The last statement included within a function must be a return statement. Return @I is wrong Return @I as a is wrong	The last statement included within a function must be a return statement. return @TableVariable is wrong.

Original Link : 15seconds

Valid

· Control-flow statements
· Assignment statements
· Variable declarations
· SELECT statements that modify local variables
· Cursor operations that fetch into local variables
· INSERT, UPDATE, DELETE statement that act upon local table variables
Invalid:

· Built-in, nondeterministic functions such as GetDate()
· Statements that update, insert, or delete tables or views
· Cursor fetch operations that return data to the client
. can’t able to execute a stored procedure.

How to Call Scalar-valued Functions

SELECT ColNames, dbo.ScalarValuedFunction(starttime,endtime) AS ColName FROM TableName
WHERE BankerID = dbo.ScalarValuedFunction('IBM')

How to Call Table-valued Functions

SELECT * FROM dbo.TableValuedFunctions('key1|key2|key3|key4|key5', '|')
-- For converting arrays to table

SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 

SELECT * FROM Department D 
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 

Handlers Module

HttpContext
- Request
- Response

HttpApplication
- maintaining application-scope methods
- data
- events
- After the HttpApplication object massages the request, it pushes the request through one or more HttpModule objects.

HttpModule

- When an HttpModule is hooked into the pipeline (via an entry in web.config), the ASP.NET runtime calls the module's Init and Dispose methods.
- Init is called when the module attaches itself to the HttpApplication object
- Dispose is called when the module is detached from HttpApplication.

Event	Occurs
AcquireRequestState	When ASP.NET acquires the current state (for example, session state) associated with the current request
AuthenticateRequest	When a security module has established the identity of the user
AuthorizeRequest	When a security module has verified user authorization
BeginRequest	When the first event in the HTTP pipeline chain of execution responds to a request
Disposed	When ASP.NET completes the chain of execution when responding to a request
EndRequest	When the last event in the HTTP pipeline chain of execution responds to a request
Error	When an unhandled exception is thrown
PostRequestHandlerExecute	When the ASP.NET handler (page, XML Web Service) finishes execution
PreRequestHandlerExecute	Just before ASP.NET begins executing a handler such as a page or XML Web Service
PreSendRequestContent	Just before ASP.NET sends content to the client
PreSendRequestHeaders	Just before ASP.NET sends HTTP headers to the client
ReleaseRequestState	After ASP.NET finishes executing all request handlers; also causes state modules to save the current state data
ResolveRequestCache	When ASP.NET completes an authorization event to let the caching modules serve requests from the cache, bypassing execution of the handler (the page or XML Web Service, for example)
UpdateRequestCache	When ASP.NET finishes executing a handler in order to let caching modules store responses that will be used to serve subsequent requests from the cache

<httpmodules>
<add name="MyHttpModule" type="name of the class which implement the interface IHttpModule, path where the class is present, Culture=neutral">

<httpmodules>
<add type="[Namespace.]SeoUrls, [AssemblyName], [Version=x.x.x.x, Culture=neutral, PublicKeyToken=933d439bb833333a]" name="SeoUrls">
</add>

<httpmodules>
<add name="MyHttpModule" type="Secure, App_Code, Culture=neutral">
</add>


<httphandlers>
<add verb="*" path="*.data" type="namespace.classname, assemblyname(I.E. DLL Name)">

<add verb="*" path="xyz.aspx" type="test.MyFactory,HandlerFactoryTest">
</add>

</add></httphandlers></httpmodules></httpmodules></add></httpmodules>