About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Monday, October 14, 2013

Pause your execution of block till a particular time using WAITFOR TIME


If you want your part of code to execute after a particular time then you can achieve this with the help of SQL Server WAITFOR TIME clause here you have mention the time along with the WAITFOR.

Let’s take a short example to see how we can achieve this. THRIST

select GETDATE() as 'Time'
WAITFOR TIME '05:40:00'
select GETDATE() as 'Delayed Time'
SELECT 'Hi its Morning'


Time
-----------------------
2013-10-10 05:39:56.440

(1 row(s) affected)

Delayed Time
-----------------------
2013-10-10 05:40:00.000

(1 row(s) affected)

Hi its Morning

Here we can see how we are able to achieve the delay of our batch using WaitFor Time.

Never miss a good chance to do good piece code.


Post Reference: Vikram Aristocratic Elfin Share

Pause your execution of block or delay execution using WAITFOR DELAY

There are situation when you want your piece of code to wait for some input after it proceed for next group of statement, if the scenario is like this you can achieve your wait using WAITFOR. So SQL Server has an option to wait for delay your batch until some short of input arrived.

Let’s take a short example to see how we can achieve this.

select GETDATE() as 'First Time'
waitfor delay '00:00:03'
select GETDATE() as '3 Second delay'
waitfor delay '00:00:02'
select GETDATE() as '2 Second delay'
waitfor delay '00:00:01'
select GETDATE() as '1 Second delay'

First Time
-----------------------
2013-10-10 05:24:39.007

(1 row(s) affected)

3 Second delay
-----------------------
2013-10-10 05:24:42.007

(1 row(s) affected)

2 Second delay
-----------------------
2013-10-10 05:24:44.007

(1 row(s) affected)

1 Second delay
-----------------------
2013-10-10 05:24:45.007

(1 row(s) affected)

Here we can see how we are able to achieve the delay of our batch using WaitFor Delay.

The way SQL developer says Database is poles apart. You database is safe as tongue in your mouth.


Post Reference: Vikram Aristocratic Elfin Share

Separating First Name and Last Name from Name


There are bargains when user want to separate first and second name from Name field generally I have seen this for creating email address for in-house projects.

So if your requirement is to separate name to first name and last name then you can easily implement it with the help of charIndex and substring function of SQL.

Let’s do a practical for the same

create table #demoSeprateName
(name varchar(20),
first_name varchar(10),
last_name varchar(10))

insert into #demoSeprateName(name) values('Neha Sharma')
insert into #demoSeprateName(name) values('Richa Sharma')

select substring(name,1,charindex(' ',name)) as 'First Name',
substring(name,charindex(' ',name) + 1,len(name)) as 'Last Name'
from #demoSeprateName

First Name           Last Name
-------------------- --------------------
Neha                 Sharma
Richa                Sharma

(2 row(s) affected)

So here we have used charIndex function, it will give the position of the specified character, and substring function with which give the subsring of a given string starting from position mention to length specified.  

Let’s update the table with first_name and last_name value

update #demoSeprateName
set first_name = substring(name,1,charindex(' ',name)),
last_name = substring(name,charindex(' ',name) + 1,len(name))

name                 first_name last_name
-------------------- ---------- ----------
Neha Sharma          Neha       Sharma
Richa Sharma         Richa      Sharma

(2 row(s) affected)

Now if you want to generate email address in the format of firstName.LastName@abc.com you can do this

select ltrim(rtrim(substring(name,1,charindex(' ',name))))
+ '.' +
ltrim(rtrim(substring(name,charindex(' ',name) + 1,len(name))))
+ '@xyz.com'
as 'Email Address'
from #demoSeprateName

Email Address
-------------------------------------------------
Neha.Sharma@xyz.com

Be serious about Coding, if you want to have any amusement in coding.


Post Reference: Vikram Aristocratic Elfin Share