Digicorp

When ever we write script or code in SQL Server we all face problem like following.

“We store the value in database with the delimiter. Now we want to use that value in the another Procedures or Views and may be to display in reports or any where else in the system. At that time we face problem in getting data out of those delimiter”

So lets see how to get the data in the table format from one column value in which values are stored with some delimiter.

Below is the table value function which gives desired output in the table format.

Parameters which need to be passed in function are:

@String = string value which needs to be formatted for e.g. string like ‘IT,Marketing,Production’. Here as we can see values are separated by comma “,”.

@Delimiter =  Delimiter like coma(,) , Pipe(|)  etc.

Function Code
==========

Create FUNCTION dbo.Split_String(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN

DECLARE @No INT
DECLARE @Item nvarchar(4000)

– Initilize the @no variable with 1
SELECT @No = 1

– if null then return
IF @String IS NULL RETURN
WHILE @No !=0
BEGIN

– get the first o
SELECT @No = CHARINDEX(@Delimiter,@String)

– get the value in the @item variable
IF @No !=0
SELECT @Item = LEFT(@STRING,@No – 1)
ELSE
SELECT @Item = @String

– Put the values in the Result Set
INSERT INTO @Results(Items) VALUES(@Item)

– Remove the item from the main string
SELECT @STRING = RIGHT(@String,LEN(@String) – @No)

– break it work done
IF LEN(@String) = 0 BREAK
END
RETURN

END

Lets check Example of  it:
==============================

Select * from dbo.Split_String(’IT,Marketing,Production’,',’)

Output in table format:
==============================

IT
Marketing
Production

Great! we have all the data in tabular format now which you can easily use anywhere else in your code.

I hope this little function will help you save lot of time and frustration. Please comment if you know any better solution for this.

Today I am going to share something I know about indexing. I am sure you all must be using it somewhere or must have heard of it.

Let’s see one wonderful example of its usage today which will show how important it is to use indexes properly in Database. I would like to thank my client and my mentor Stephen Fegel of Galatea Systems for imparting this wonderful knowledge.

We all use “Select” for retrieving data from the tables. In most cases if data is not too much it takes minimal time to fetch records without indexing also. But in case of large data this may be a severe problem.

Lets take an example:

I have two tables one is houses which is my main table and it has 25,666 records.

And another table is house_desc which has 6,04,255 records.

And the query is

SELECT
SQL_NO_CACHE h.*,
(SELECT
SQL_NO_CACHE desc_value
FROM
house_desc AS d
WHERE
language_id = ‘EN’
AND
d.house_id = h.house_id
AND
(
desc_type = ‘description’
OR
desc_type = ‘introduction’
)
ORDER BY desc_type
LIMIT 0 , 1
)
AS
desc_value
FROM
houses AS h
WHERE
house_id
IN (’12345678′)

Now lets join both the tables and fire sub query for retrieving data from house_desc table.

Structure of the house_desc is

`c_code` varchar(50)
`house_id` int(10)
`country` char(2)
`language_id` enum(’NL’,'EN’,'DE’,'FR’,'ES’,'IT’)
`desc_type` varchar(50)
`desc_value` longtext

So when I fire the query without indexing and I get following result

Showing rows 0 – 0 (1 total, Query took 13.0067 sec)

It returns only 1 row but still it took 13 secs!!

Now just see the magic of indexing here. Just set the index on house_id field
Fire the same query again and here is the result:

Showing rows 0 – 0 (1 total, Query took 0.0899 sec)

Output is same but just look at the difference in time to fetch records!

Before indexing it took 13.0067 seconds and after indexing it took 0.0899 seconds i.e. it is 1000 time faster than without indexing.

The result may be different in your case as in sometimes it can be 100 time faster or 10 time faster but one thing is for sure that it will be faster than without indexing (if done correctly!).

When you have large amount of records in your table and you want to search that table then it is better to set index on those fields which are there in where clause.

You can’t set index on all the fields from table. It is also very important thing on which filed you set the index.

You can read more on indexing from this
http://www.informit.com/articles/article.aspx?p=377652

How to connect database with application running in QTP to check data in DB dynamically?

An example is shown connecting QTP with SQL Server

A simple script is shown below with necessary comments :

Dim con,rs,column_count,column_name,i,column_value

Set con=createobject(”adodb.connection”) // Its a connection object
Set rs=createobject(”adodb.recordset”) // A record set should be defined

// Necessary connection string should be their
con.open “provider=sqloledb.1;server=.\SQLEXPRESS;database=DBNAME;user id=XYZ;password=XYZ”

// Finally the record set is defined to fetch the value of particular fields according to the query passed.

rs.open”select * from tbl_newsMaster where username=’admin’”

// Checks the record set till the end until any record is found.If any record is found then it stores the values in “rs” record set.
Do while not rs.eof
For i = 0 To rs.Fields.Count -1
‘ msgbox rs.Fields.Count //counts the number of records in record set “rs”
column_value=rs.Fields(i).value //Fetches the value of particular field stored in record set “rs”
column_name=rs.Fields(i).name
Msgbox “Record Exist :” & column_name&” ” &column_value
next
rs.movenext
Loop

Introduction

When we write Dynamic queries, most of the time only “WHERE” clause needs to be dynamic in the stored procedure rest of the part remains static, I mean “SELECT” and “FROM” Clause remain static most of the time.

To write dynamic query, we are choosing variables so that we can write “WHERE” clause using appropriate conditions and then putting a query in variables. Writing queries like this generates so much confusion while debugging and maintenance of the query.

Here is one of the process by which we can eliminate use of variable while writing queries which provide the same result as a dynamically written query provides.

We can use this technique if the “SELECT” list is going to be static and we need to take care of only “WHERE” clause. Otherwise we can use variables to store SQL query if the columns in the “SELECT” list will be generated dynamically and/or name of the source table will be decided at runtime. To understand this approach let us see one example.

Example

Original Stored Procedure:

We have one stored procedure as below

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

– exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

– exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[SC_Report_GetDataByCustomerLounge]

@CustomerId int = 0,

@LoungeId int = 0,

@CountryId int = 0,

@FromDate datetime,

@ToDate datetime

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition = ”

BEGIN

IF(@CustomerId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CustomerId = ‘ + Convert(varchar,@CustomerId)

END

IF(@LoungeId != 0 AND @WhereCondition != ”)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

ELSE IF(@LoungeId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

IF(@CountryId != 0 AND @WhereCondition != ”)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

ELSE IF(@CountryId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

IF (@FromDate != ‘01/01/1900′ AND @ToDate != ‘01/01/1900′ AND @WhereCondition != ”)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND

Convert(varchar(10),Trans.AccessDate,101) <= ”’ +

Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

ELSE IF (@FromDate != ‘01/01/1900′ AND @ToDate != ‘01/01/1900′)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND Convert(varchar(10),

Trans.AccessDate,101) <= ”’ + Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

IF(@WhereCondition != ”)

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE ‘ + @WhereCondition

END

ELSE

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId ‘

END

PRINT @StrQuery

exec sp_executesql @StrQuery

END

From above stored procedure we can see that SELECT query part is static and the query is building dynamically as per change in the WHERE Clause. The above query gives me the following output.

Result before applying alternative approch

Alternative way to write same query:

We will be able to replace the above stored procedure using the following stored procedure and that will give same result as previous one.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

– exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

– exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[Test]

@CustomerId int = NULL,

@LoungeId int = NULL,

@CountryId int = NULL,

@FromDate datetime = NULL,

@ToDate datetime = NULL

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition = ”

BEGIN

Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ‘ ‘ + Cust.MiddleName + ‘ ‘+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE (Trans.CustomerId = Convert(varchar,@CustomerId) OR Trans.CustomerId = NULL)

AND (Trans.LoungeId = Convert(varchar,@LoungeId) OR Trans.LoungeId = NULL)

AND (Trans.CountryId = Convert(varchar,@CountryId) OR Trans.CountryId = NULL)

AND (Convert(varchar(10),Trans.AccessDate,101) >= Convert(varchar,Convert(varchar(10),@FromDate,101))

AND Convert(varchar(10),Trans.AccessDate,101) <= Convert(varchar,Convert(varchar(10),@ToDate,101)))

END

The above stored procedure gives me the following output:

Result after applying alternative approch

The benefits of using this technique are:

• It will reduce the complexity of query.
• Using this, we can easily maintain the query.
• Syntax checking will be much simpler as most of the time quote(‘) gives problem to novice developers when query is stored in a variable.
• And most important, when we use LINQ –to-SQL in our application structure it will reduce the conversion error between ISingleResult and INT in the case of writing dynamic query.

References:

www.SQLServerCentral.com

How to Enable/Install Spell Checker in Firefox:

(1) Go to https://addons.mozilla.org/en-US/firefox/addon/3497

(2) Browse through the page and click add to Firefox.

(3) Software installation Dialog box will appear and click install.

(4) After installation completed, click restart Firefox.

In order to enable/disable spell check Right Click on the composing area and click spell checking to enable or disable it.
How to lookup for Definition:

(1) Go to https://addons.mozilla.org/en-US/firefox/addon/2083

(2) Browse through the page and click add to Firefox.

(3) Software installation Dialog box will appear and click install.

(4) After installation completed, click restart Firefox.

While composing select the word, right click on it and select “Inline Definitions” From popup menu. You will see the meaning of word.

With HTML pages on the web, you can just include the required Javascript between <script> and </script> tags.

When you validate the HTML on your web page, the Javascript content is considered to be CDATA (character data), therefore ignored by the validator. But this is not true, if you follow the XHTML standards in creating your web page.

With XHTML, the code between the script tags is considered to be PCDATA (parsed character data) which is processed by the validator. Because of which, you cannot include Javascript between the script tags on your page(I mean inline javascript), without breaking your web page; as far as the validator is concerned.

This problem can be fixed by 2 different ways.

Make the Javascript external to the page, so that there is nothing between the script tags to stop the page validating.

Moreover, If it is just one or two lines of code, then it is probably not worth making an external script. So you would prefer to keep the content between the script tags and ask the validator to ignore the particular code. This can be done by placing the Javascript code within a CDATA tag like this:

This fixes the validator.
The problem is that, some older web browsers don’t understand the CDATA tag and so this breaks your Javascript in those web browsers. Fortunately, Javascript multi-line comments start and finish with characters that do not cause validation errors. We can therefore comment out the CDATA tag, while leaving the Javascript content defined as CDATA as far as the validator is concerned. We do it like this:

Now your Javascript code will be handled correctly by both newer browsers and validators that actually understand the XHTML DTD as well as by older web browsers that don’t understand XHTML and consider everything on the page as HTML.

Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.

For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.

Today, I will explain how to get data from Dataset into Sqlserver Table.

– Code for .Net

Dim ds as new Dataset

Dim dt as new Table

dt.TableName = “MyTable”

ds.Tables.add(dt)

Dim lStrWriter As New StringWriter

Dim lStr As String

ds.WriteXML(lStrWriter)

lstr = lStrWriter.Tostring

Pass this lstr variable to the procedure as parameter – @xmldoc.

Now write the code procedure to get the data from Dataset.

— Code in Sqlserver Store Procedure

CREATE PROCEDURE [dbo].[TestXML]

@xmldoc text — Give Text datatype for getting xml from dataset

as

———Read the Datset  From XML —————–

– call sp_xml_preparedocument

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #TestTable

FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)

WITH (pk_Userid  BigInt,

UserName  Varchar(10))

EXEC sp_xml_removedocument @idoc

———- End Read ———-

END

BY the use of this procedure you get all the data from dataset in to the ”#Testtable” and we can use this as Sqlserver table in procedure.

I hope this article will be useful to you, Please give feedback on this article.

When developing desktop based or similar applications, it becomes headache to synchronize data between different pages. Say when Delete is clicked in context menu of some node of tree, Grid and some other panels need to be notified. Here is one simple way for accessing data of other panels. We have singleton session object (only one object at runtime) at each client’s runtime. So when we have some data that need to be used in other pages, we can put those objects in the session map and access it from the other class. Here is how we can create class that gives singleton instance of session map.


import java.util.HashMap;
public class SessionFactory {

private static HashMap session;
private SessionFactory(){
}

public static HashMap getSessionInstance(){
if(session==null){
session = new HashMap();
}

return session;
}

}

Today, we will examine something very simple and very generic that can apply to hordes of programming languages. Let’s take a common question that is frequently discussed – What is difference between Line Feed (\n) and Carriage Return (\r)?

Prior to continuing with this article let us first look into few synonyms for LF and CR.

Line Feed – LF – \n – 0×0a – 10 (decimal)

Carriage Return – CR – \r – 0×0D – 13 (decimal)

Now that we have understood that we have two different options to get new line, the question that arises is – why is it so?

The reason is simple. Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. Our favorite OS windows needs both the characters together to interpret as new line, which is ‘\r\n’. This is the reason why a file created in one OS does not open properly in another OS and makes it messy.

Now, let us see how we can create a new line in SQL Server. It is a very simple script yet very useful when we have to do run print something or generate scripts. I have illustrated two examples below that are very easy to understand. In the first example, there are no new line chars inserted and for the same, everything is displayed in a single line. However, in the second example, new line char is inserted and the lines are separated with a new line.

Example 1: No new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
GO

Example 2: With new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
GO


I hope my examples make things more clear to you all. Let me have your feedback on this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)