Pagging in sql

In one of my older post i posted about  paging in sql using Row_Number function (http://simplyasp.blogspot.in/2010/11/paging-records-using-sql-server-2005.html).on latest version of sql they had improved the paging functionality by bothe performance base and also in readbility base too. on 2008  they introduce CTE (Common table expression) and Derived table.  on sql 2012 they done a mass step on that FETCH NEXT functionality ... i love that.

Below i show all the method of paging i mentioned above.

--SQL 2005/2008 Paging Method Using Derived Table

DECLARE @Start INT

DECLARE @End INT

SELECT @Start = 14000,@End = 14050

 

SELECT LastName, FirstName, EmailAddress

FROM (SELECT LastName, FirstName, EmailAddress,

      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber

      FROM Employee) EmployeePage

WHERE RowNumber > @Start AND RowNumber <= @End

ORDER BY LastName, FirstName, EmailAddress

GO

 

--SQL 2005/2008 Paging Method Using CTE

DECLARE @Start INT

DECLARE @End INT

SELECT @Start = 14000,@End = 14050;

 

WITH EmployeePage AS

(SELECT LastName, FirstName, EmailAddress,

 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber

 FROM Employee)

SELECT LastName, FirstName, EmailAddress

FROM EmployeePage

WHERE RowNumber > @Start AND RowNumber <= @End

ORDER BY LastName, FirstName, EmailAddress

GO

 

--SQL SERVER 2012

SELECT LastName, FirstName, EmailAddress

FROM Employee

ORDER BY LastName, FirstName, EmailAddress

OFFSET 14000 ROWS

FETCH NEXT 50 ROWS ONLY;

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Sanitize database inputs

A lesson to all developers 

Sanitize database inputs

 


If u had any trouble just ask, Happy to help u :)
 Stay Tune... Have a nice day... 'N happy Coding :)

Passing store procedure result to another store procedure as parameter

Passing store procedure result to another store procedure as parameter

-- First Stored ProcedureCREATE PROCEDURE SquareSP@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam-- Additional CodeRETURN (@MyFirstParamSquare)GO

get current date in storeprocedure

cast(getdate() as date)
simple and sexy isit?

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Better way for multiple optional search in sql Store Procedure

Better way for multiple optional search in sql Store Procedure

i assigned multiple optional search to my junior resources a few weeks ago recently i gone through the code standards I'm bit surprised every one is different :) . they gone through row-filtering,Dynamic sql,where case in sql,Table search,IF IS NULL in sql ...

Get Store Procedure,User Table,System Table,Primary Key,Scalar Function Create date and modification date in SQL SERVER

Get Store Procedure,User Table,System Table,Primary Key,Scalar Function Create date and modification date in SQL SERVER

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Loop in sql with break or Exit Statement

 Loop in sql with break or Exit Statement

 Loop in sql

Loop with break or Exit Statement

LEN and DATALENGTH in SQL Server

Difference between LEN and DATALENGTH in SQL Server

Len will trim the end of the string and count the length where as  DATALENGTH just count the lenght,its doesn't trim :)

Below sample query and example

QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?

ANSI NULL ON/OFF:
This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

If u had any trouble just ask, Happy to help u :) Stay Tune... Have a nice day... 'N happy Coding :)

Remove Duplicate record from Table

Hi *.*,

create table #tbdemo (

id int identity(1,1),

StrName Nvarchar(50),

StrCity Nvarchar(50)

)

insert into #tbdemo values ('Arun','Kollam')

insert into #tbdemo values('Arun','Kollam')

insert into #tbdemo values('Prajeesh','Kochi')

insert into #tbdemo values('Prajeesh','Kochi')

insert into #tbdemo values('Sibi','Patnapuram')

insert into #tbdemo values('Sibi','Patnapuram')

insert into #tbdemo values('Dasapan','Kozikodi')

insert into #tbdemo values('Dasapan','Kozikodi')

select * from #tbdemo

select MIN(id) as ID from #tbdemo

GROUP BY StrName,StrCity

DELETE FROM #tbdemo

WHERE id NOT IN(select MIN(id) as ID from #tbdemo GROUP BY StrName,StrCity)

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Assign Value to a variable on Store Procedure

Hi *.*,
Assign Value to a variable on Store Procedure. First declare a variable with prefix '@'.Then you can use both 
SET and SELECT.

ERROR: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Hi *.*,

Yet another common error while handling with datetime in sql.Worst part of this error is on code side and sql site we had given datetime as datatype.Still its their !!!!

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Hi *.*,
This is one of the most frequently asking question on sql/asp fourms.
Reasons for this error:

1. Make sure your connection string data are correct and only that particular string is you calling.
2. You need to enable the listener on port 1433 in the SQL Server Configuration Manager control panel
3. Enable the "sa" user. It's disabled by default after the install.
4. Set a password on the "sa" user.
5. Enable SQL Authentication + NT Authentication mode.
6. Make sure the Windows firewall isn't blocking port 1433.
7. Try the default instance name instead of SQLEXPRESS.
8. Make sure the remote connection is using TCP/1433

If u had any trouble just ask, Happy to help u :) 
Stay Tune... 
Have a nice day... 'N happy Coding :)

Difference between GETDATE() and GETUTCDATE()

Hi *.*,
Playing with date-time is one of the biggest headache to me. :( . i faced a problem recently with that. Server is located at one place and client at another place  so what happened is the date doesn't change for 4.15 hrs!!!!.
Later v solved by adding GMT along with that.
Here i'm sharing the SQL script helped to get out of that.

NULLIF function in sql

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. 

Table Sales_Data

Store_name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:

SELECT Store_name, NULLIF(Actual,Goal) FROM Sales_Data;

The result is:

Store_name	NULLIF(Actual,Goal)
Store A	NULL
Store B	40
Store C	25

Stay Tune.

Have a nice day... 'N happy Coding :)

Coalesce Function in sql

The COALESCE function in SQL returns the first non-NULL expression among its arguments.

 

Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

and we want to find out the best way to contact each person according to the following rules:

1. If a person has a business phone, use the business phone number.

2. If a person does not have a business phone and has a cell phone, use the cell phone number.

3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;

Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

Have a nice day... 'N happy Coding :)