Using CHARINDEX in SQL Query

CHARINDEX returns a starting position of a string which helps developer to split a data based on th erequirement. Below is an example of CHARINDEX usage:

DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange
The output is:
----------------------------------
MinRange      MaxRange
-----------------------------------
50                   70
-----------------------------------

Adding Serial Number to SSRS Report via RowNumber() function

Add a new column as 'S.No.' in SSRS report. Open Expression window of the column and enter below text:

=RowNumber("<Datasetname>")

E.g., If my dataset name is "MyData"

then the command will be:
=RowNumber("MyData")

Using Multi-Value Parameter in SSRS via Stored Procedure

It has been found that in SSRS 2005-2012, when we use direct query as data set we are able to apply multi-value parameter and the report works when we select multiple values in parameter.

E.g., Let the direct query be as follows:

SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country in (@Country)

When we use the above query in dataset a parameter Country will be created in Parameter list, and we can configure parameter as shown below, the report work well when we select multiple values in parameter.

When the same query is executed as stored procedure, the report will fail when we select multiple values in parameter. To overcome this issue we need to create the given function in our database:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MultiParameter]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

And we need to modify the stored procedure as like below:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Mahadevan
-- Create date: May 27, 2012
-- Description: To list of Customer by Countrywise
-- =============================================
CREATE PROCEDURE [dbo].[Rpt_Customer]
 @Country NVARCHAR(MAX)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
  WHERE Country IN (Select * from [dbo].[MultiParameter] (@Country, ',') )

END
GO

 Now configure the report parameter to select multi-values, the report will work well.

'Sequence' New Feature in SQL 2012

Sequence is an user defined object that generates a sequence of a number.

/****** Create new Sequence Object ******/
 CREATE SEQUENCE FirstSequence
 START WITH 1
 INCREMENT BY 1;


/****** Create Temp Table ******/
 DECLARE @Employee TABLE
 (
 ID int NOT NULL PRIMARY KEY,
 Employee nvarchar(100) NOT NULL
 );

/****** Insert Some Data ******/
 INSERT @Employee (ID, Employee)
 VALUES (NEXT VALUE FOR MySequence, 'Maha'),
 (NEXT VALUE FOR MySequence, 'Sam'),
 (NEXT VALUE FOR MySequence, 'Ram');

/****** List the Data ******/
SELECT * FROM @Employee;

The output will be as follows:-------------
ID  Employee
-------------
1 Maha
2   Sam
3 Ram
-------------

SQL Geometry Data Type example

SQL Server 2008 Date & Time Data Type

DATE: This data type is useful to store the dates without the time part, we can store dates starting from 00001-01-01 through 9999-12-31 i.e. January 1, 1 A.D. through December 31, 9999 A.D. It supports the Gregorian Calendar and uses 3 bytes to store the date.

DATETIME: This is a well known data type by most of us the date range supported is 01-01-1753 through 9999-12-31 or January 1, 1753, through December 31, 9999 and time range supported is 00:00:00 through 23:59:59.997. It takes 8 bytes to store the date/time data.

SMALLDATETIME: This is a data type that has the accuracy to 1 minute and useful for storing dates and time when the precision doesn't matter too much for example. The order booking date and time of a user. The range supported by this type of data type is 1990-01-01 through 2079-06-06 or January 1, 1900, through June 6, 2079 and time range between 00:00:00 through 23:59:59. The data type takes 4 fixed bytes to store the data.

DATETIME2: This is a new data type introduced in SQL Server 2008 and this date/time data type is introduced to store the high precision date and time data. The data type can be defined for variable lengths depending on the requirement. This data type also follows the Gregorian Calendar. The Time Zone can't be specified in this data type. This is still useful because it gives you a complete flexibility to store the date time data as per your requirement.

DATETIMEOFFSET: This is the new data type that is included in SQL Server 2008 and this data type is the most advanced in the league. We can store high precision date/ time with the Date Time Offset. We can't store the Time Zone like Eastern Time, Central Time etc. in the data type but can store the offset -5:00 for EST and -6:00 CST and so on.
      The date range is between 0001-01-01 and 9999-12-31 or January 1,1 A.D. through December 31, 9999 A.D. and the Time Range is between 00:00:00 and 23:59:59.9999999. The offset range is between -14:00 through +14:00. The precision of the data type can be set manually and it follows the Gregorian Calendar.

TIME: Allows only Time format data.